<?php
class KqbanhangttsController extends AppController {

	var $name = 'Kqbanhangtts';
	var $helpers=array('Ctable','Html','Form','Js','FileUpload.FileUpload','Others');
//	var $components = array('RequestHandler');
    var $components = array('RequestHandler','FileUpload.FileUpload');
   
	var $uses=array('Kqbanhangtt','Chitieudv','Chitieunv','Kqbanhang','Hanghoabhld','Nhanvien',
		'Thoigian','Loaikenh','Zluykenv','Kehoach','Phankenh','Kenh','Thuocdv','Kehoachbhld','Zthbhkenh','Zdv');
	
	function index() {
		$this->Kqbanhangtt->recursive = 0;
		$this->set('kqbanhangtts', $this->paginate());
	}

	function nhapkqbanhang()
	{
		$donvi_id = $this->Session-> read('cur_dv');
    	/*
    	 * lay chi tieu duoc giao trong thang
    	 */
    	$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>date('Y-m-d'), 'ngaykt >='=>date('Y-m-d'),'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
    	$ngay=date('Y-m-d');
    	// rows
    	$chitieunvs = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Kehoach.donvi_id'=>$donvi_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
    	
    	$nhanviens = $this->Kqbanhang->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));
    	
    	
    	/*
    	 * lay kenh co bao cao trong ngay
    	 */
    	$pkngay= $this->Kqbanhang->find('list',array(
    										'recursive'=>0,
    										'conditions'=>array('ngay'=>$ngay,'Phankenh.nhanvien_id'=>$nhanviens),
    										'fields'=>array('phankenh_id'),
    										'group'=>array('phankenh_id')));
    	$lks = $this->Phankenh->find('list',array(
	    										'recursive'=>0,'contain'=>array('kenh'),
	    										'conditions'=>array('Phankenh.id'=>$pkngay),
	    										'fields'=>array('Kenh.loaikenh_id'),
	    										'group'=>array('Kenh.loaikenh_id')));
    	//cols
    	$loaikenhnvs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
    	$loaikenhnvs[]="Tổng";
    	$qstr='SELECT k.loaikenh_id, ct.hanghoa_id, sum( kq.soluong ) as soluong
				FROM kqbanhangs kq, phankenhs pk, kenhs k, chitieunvs ct, nhanviens nv
				WHERE kq.phankenh_id = pk.id
					AND pk.kenh_id = k.id
					AND kq.chitieunv_id = ct.id
					AND ct.nhanvien_id = nv.id
					AND nv.donvi_id ='.$donvi_id .'
					 AND kq.ngay = "'.$ngay.'" GROUP BY k.loaikenh_id, ct.hanghoa_id';
    		$dt=$this->Kqbanhang->query($qstr);
    		if (!empty($dt))
    		{
    			$row=0;
    			$col=0;
	    		foreach ($chitieunvs as $keyct => $valuect):
	    		$col=0;
	    		$tong=0;
	    			foreach ($loaikenhnvs as $keylk => $valuelk):
	    				for($i=0;$i<count($dt);$i++):
	    					if ($keyct == $dt[$i]['ct']['hanghoa_id'] && $keylk == $dt[$i]['k']['loaikenh_id'])
	    					{ 
	    						$datanv[$row][$col]=$dt[$i][0]['soluong'];
	    						$tong +=$datanv[$row][$col];
	    					} 
	    				endfor;
	    				$col++;
	    			endforeach;
	    			$datanv[$row][--$col]=$tong;
	    			$row++;
	    		endforeach;
    		}
		/*
		 * table
		 */
		$ngay = date("Y-m-d");
		$cts = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id')));
    	$dabc = $this->Kqbanhangtt->find('list',array(
    												'conditions'=>array('chitieudv_id'=>$cts,'ngay'=>$ngay),
    												'fields'=>array('id')));
    	if (count($dabc)!=0)
    	{
    		$this->Session->setFlash(__('Báo Cáo Đã Được Lập',true));
    		$this->redirect(array('action' => 'hienkqngay',$donvi_id));
    	}
    	
    	$chitieus = $this->Chitieudv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    	/*$kenhs = $this->Phankenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Nhanvien'),
    											'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
    											'fields'=>array('Phankenh.kenh_id')
    												));
    	$loaikenhs = $this->Kenh->find('list',array(
    												'recursive'=>0,'contain'=>array('Loaikenh'),
    												'conditions'=>array('Kenh.id'=>$kenhs),
    												'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));*/
    	$loaikenhs = $this->Loaikenh->find('list',array(
    												'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
		$this->set(compact('chitieunvs','loaikenhnvs','datanv'));
    	$this->set(compact('donvi_id','col_ctdv','row_ctdv','datadv','thoigian','loaikenhs','chitieus'));
	}
	
	function suakqbanhang()
	{
		//if()
		/*
		 * lay tong hop tu nv
		 */
		$donvi_id = $this->Session->read('cur_dv');
    	/*
    	 * lay chi tieu duoc giao trong thang
    	 */
	$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>date('Y-m-d'), 'ngaykt >='=>date('Y-m-d'),'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
    	$ngay=date('Y-m-d');
    	// rows
    	$chitieunvs = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Kehoach.donvi_id'=>$donvi_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
    	
    	$nhanviens = $this->Kqbanhang->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));
    	
    	
    	/*
    	 * lay kenh co bao cao trong ngay
    	 */
    	$pkngay= $this->Kqbanhang->find('list',array(
    										'recursive'=>0,
    										'conditions'=>array('ngay'=>$ngay,'Phankenh.nhanvien_id'=>$nhanviens),
    										'fields'=>array('phankenh_id'),
    										'group'=>array('phankenh_id')));
    	$lks = $this->Phankenh->find('list',array(
	    										'recursive'=>0,'contain'=>array('kenh'),
	    										'conditions'=>array('Phankenh.id'=>$pkngay),
	    										'fields'=>array('Kenh.loaikenh_id'),
	    										'group'=>array('Kenh.loaikenh_id')));
    	//cols
    	$loaikenhnvs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
    	$loaikenhnvs[]="Tổng";
    	$qstr='SELECT k.loaikenh_id, ct.hanghoa_id, sum( kq.soluong ) as soluong
				FROM kqbanhangs kq, phankenhs pk, kenhs k, chitieunvs ct, nhanviens nv
				WHERE kq.phankenh_id = pk.id
					AND pk.kenh_id = k.id
					AND kq.chitieunv_id = ct.id
					AND ct.nhanvien_id = nv.id
					AND nv.donvi_id ='.$donvi_id .'
					 AND kq.ngay = "'.$ngay.'" GROUP BY k.loaikenh_id, ct.hanghoa_id';
    		$dt=$this->Kqbanhang->query($qstr);
    		if (!empty($dt))
    		{
    			$row=0;
    			$col=0;
	    		foreach ($chitieunvs as $keyct => $valuect):
	    		$col=0;
	    		$tong=0;
	    			foreach ($loaikenhnvs as $keylk => $valuelk):
	    				for($i=0;$i<count($dt);$i++):
	    					if ($keyct == $dt[$i]['ct']['hanghoa_id'] && $keylk == $dt[$i]['k']['loaikenh_id'])
	    					{ 
	    						$datanv[$row][$col]=$dt[$i][0]['soluong'];
	    						$tong +=$datanv[$row][$col];
	    					} 
	    				endfor;
	    				$col++;
	    			endforeach;
	    			$datanv[$row][--$col]=$tong;
	    			$row++;
	    		endforeach;
    		}
    	/*
    	 * lay data da bao cao
    	 */
    	$chitieus = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id')));
    	$kqbh = $this->Kqbanhangtt->find('list',array(
    												'conditions'=>array('chitieudv_id'=>$chitieus,'ngay'=>$ngay),
    												'fields'=>array('loaikenh_id','soluong','chitieudv_id')));
    	$kqbh_id= $this->Kqbanhangtt->find('list',array(
    												'conditions'=>array('chitieudv_id'=>$chitieus,'ngay'=>$ngay),
    												'fields'=>array('loaikenh_id','id','chitieudv_id')));
    	$chitieudvs = $this->Chitieudv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    	/*$kenhs = $this->Phankenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Nhanvien'),
    											'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
    											'fields'=>array('Phankenh.kenh_id')
    												));
    	$loaikenhdvs = $this->Kenh->find('list',array(
    												'recursive'=>0,'contain'=>array('Loaikenh'),
    												'conditions'=>array('Kenh.id'=>$kenhs),
    												'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));*/
    	
    	$lkngays= $this->Kqbanhangtt->find('list',array(
    										'recursive'=>0,
    										'conditions'=>array('ngay'=>$ngay,'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('loaikenh_id'),
    										'group'=>array('loaikenh_id')));
    	//cols
    	$loaikenhdvs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lkngays),
	    												'fields'=>array('id','loaikenh')));
    	$cot=0;
		$dong=0;
		if (!empty($kqbh))
		{
		foreach ($loaikenhdvs as $keylk=>$valuelk)
		{	
			$dong=0;
			foreach ($chitieudvs as $keyct=>$valuect)
			{
				$datadvsl[$dong][$cot] = $kqbh[$keyct][$keylk];
				$datadvid[$dong][$cot] = $kqbh_id[$keyct][$keylk];
				$dong++;
			}
			$cot++;
		}
		}	
		$this->set(compact('chitieunvs','loaikenhnvs','datanv'));
		$this->set(compact('chitieudvs','loaikenhdvs','datadvid','datadvsl','donvi_id'));
	}
	
	function luukq()
	{
		$colnum = $this->data['Kqbanhangtt']['colnum'];
		$rownum = $this->data['Kqbanhangtt']['rownum'];
		
		$ngay = date("Y-m-d");
		$donvi_id = $this->Session->read('cur_dv');
		for($i=0;$i<$colnum;$i++)
             for($j=0;$j<$rownum;$j++)
             {
					if(isset($this->data['Kqbanhangtt']['id'][$j][$i]))
						$record['Kqbanhangtt']['id']=$this->data['Kqbanhangtt']['id'][$j][$i]; 
             		$record['Kqbanhangtt']['ngay']=$ngay;
                    $record['Kqbanhangtt']['loaikenh_id']=$this->data['Kqbanhangtt']['col'][$i];
                    $record['Kqbanhangtt']['chitieudv_id']=$this->data['Kqbanhangtt']['row'][$j];
                    if ($this->data['Kqbanhangtt'][$j][$i]!=NULL)
                    	$record['Kqbanhangtt']['soluong']=$this->data['Kqbanhangtt'][$j][$i];
                    else
                    	$record['Kqbanhangtt']['soluong']=0;
                    if(!$this->Kqbanhangtt->saveAll($record))
                    {
                         $this->Session->setFlash(__('Lưu Thất Bại', true));                     
             		}
    		}
    		$this->Session->setFlash(__('Kết Quả Vừa Báo Cáo', true));
       		$this->redirect(array('action' => 'hienkqngay',$donvi_id));
	}
	
	function hienkqngay($donvi_id = 0)
	{
		$ngay = date("Y-m-d");
		$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>date('Y-m-d'), 'ngaykt >='=>date('Y-m-d'),'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
    	/*
    	 * lay data trung tam bao cao hom nay
    	 */
		$nhanviens = $this->Kqbanhang->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));
    	
    	$lks = $this->Phankenh->find('list',array(
	    										'recursive'=>0,'contain'=>array('kenh'),
	    										'conditions'=>array('Phankenh.nhanvien_id'=>$nhanviens),
	    										'fields'=>array('Kenh.loaikenh_id'),
	    										'group'=>array('Kenh.loaikenh_id')));
    	//cols
    	$loaikenhdvs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
    	//rows
    	$chitieudvs = $this->Chitieudv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
    	
    	$data = $this->Kqbanhangtt->find('list',array(
    												'recursive'=>0,'contain'=>array('Chitieudv'),
    												'conditions'=>array('Chitieudv.donvi_id'=>$donvi_id,'ngay'=>$ngay),
    												'fields'=>array('loaikenh_id','soluong','Chitieudv.hanghoa_id')));
		if(!empty($data))
		{
    	//$data
		$loaikenhdvs['tong']='Tổng';
    	$hang=0;
		$cot=0;
    	foreach ($chitieudvs as $keyct =>$valuect):
    		$cot=0;
    		$tong=0;
			foreach ($loaikenhdvs as $keylk =>$valuelk):
				if ($keylk!='tong')
				{
					$databc[$hang][$cot]=$data[$keyct][$keylk];
					$tong+= $databc[$hang][$cot];
				}	
				else 
					$databc[$hang][$cot] = $tong;
				$cot++;
			endforeach;
			$hang++;
		endforeach;
		}	
    	$this->set(compact('loaikenhdvs','chitieudvs','databc'));
	}

	function kqbhngay($donvi_id =0)
	{
		$this->set(compact('donvi_id'));
	}
	
	function kqngay($donvi_id = 0)
	{
		$this->layout='ajax';
		$ngay = $this->data['Kqbanhangtt']['ngay'];
		$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>$ngay, 'ngaykt >='=>$ngay,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
		$chitieus = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id')));
    	$kqbh = $this->Kqbanhangtt->find('list',array(
    												'conditions'=>array('chitieudv_id'=>$chitieus,'ngay'=>$ngay),
    												'fields'=>array('loaikenh_id','soluong','chitieudv_id')));
    	$chitieudvs = $this->Chitieudv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    	$kenhs = $this->Phankenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Nhanvien'),
    											'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
    											'fields'=>array('Phankenh.kenh_id')
    												));
    	$loaikenhdvs = $this->Kenh->find('list',array(
    												'recursive'=>0,'contain'=>array('Loaikenh'),
    												'conditions'=>array('Kenh.id'=>$kenhs),
    												'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    	if (!empty($kqbh))
    	{
	    	$cot=0;
			$dong=0;
			$loaikenhdvs['tong']='Tổng';
			foreach ($chitieudvs as $keyct=>$valuect):
				$cot=0;
				$tong=0;
				foreach ($loaikenhdvs as $keylk=>$valuelk):
					if($keylk!='tong')
					{
						$datadvsl[$dong][$cot] = $kqbh[$keyct][$keylk];
						$tong +=$datadvsl[$dong][$cot];
					}
					else
					{
						$datadvsl[$dong][$cot] = $tong;
					}
					$cot++;
				endforeach;
				$dong++;				
			endforeach;
    	}
		$this->set(compact('loaikenhdvs','chitieudvs','datadvsl'));
	}

	function kqbhth_tuan($donvi_id=0)
	{
		$kh = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach'),
    											'conditions'=>array('Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('Kehoach.thoigian_id'),
    											'group'=>array('Kehoach.thoigian_id')));	
    	$thoigian=$this->Thoigian->find('list',array('conditions'=>array('id'=>$kh)));
    	$this->set(compact('thoigian','donvi_id'));
	}

	function laykqbh1($donvi_id=0)
	{
		$this->layout='ajax';
		$ngaybd = $this->data['Kqbanhangtt']['ngaybd'];
		$ngaykt = $this->data['Kqbanhangtt']['ngaykt'];
		if (date('m',strtotime($ngaybd))!=date('m',strtotime($ngaykt)))
		{
			echo "<h2>ngày bắt đầu và ngày kết thúc phải cùng Tháng</h2>";
		}
		else
		{
			$donvi = $this->Kqbanhangtt->Chitieudv->Donvi->find('all',array('conditions'=>array('id'=>$donvi_id)));
			$ngaybd=date('Y-m-d',strtotime($ngaybd));
			$ngaykt=date('Y-m-d',strtotime($ngaykt));
			$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>$ngaybd, 'ngaykt >='=>$ngaykt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
			$chitieus = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id')));
			
    		$kqbhs = $this->Kqbanhangtt->find('all',array(
    												'conditions'=>array('chitieudv_id'=>$chitieus,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    												'fields'=>array('chitieudv_id','loaikenh_id','sum(Kqbanhangtt.soluong) as soluong'),
    												'group'=>array('chitieudv_id','loaikenh_id')));
    		$chitieudvs = $this->Chitieudv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    		$kenhs = $this->Phankenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Nhanvien'),
    											'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
    											'fields'=>array('Phankenh.kenh_id')
    												));
    		$loaikenhdvs = $this->Kenh->find('list',array(
    												'recursive'=>0,'contain'=>array('Loaikenh'),
    												'conditions'=>array('Kenh.id'=>$kenhs),
    												'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    		
    		$kehoach = $this->Chitieudv->find('list',array(
    										'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    										'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')));
    		
	    	if (!empty($kqbhs))
	    	{
		    	$cot=0;
				$dong=0;
				$loaikenhdvs['tong']='Tổng';
				$loaikenhdvs['kehoach']='Kế Hoạch';
				$loaikenhdvs['hoanthanh']='% Hoàn Thành';
				$kqbh = array();
				foreach ($kqbhs as $kq)
				{
					$kqbh[$kq['Kqbanhangtt']['chitieudv_id']][$kq['Kqbanhangtt']['loaikenh_id']]=$kq[0]['soluong'];
				}
				foreach ($chitieudvs as $keyct=>$valuect):
					$cot=0;
					$tong=0;
					foreach ($loaikenhdvs as $keylk=>$valuelk):
						if($keylk!='tong' && $keylk!='kehoach' && $keylk!='hoanthanh')
						{
							$datadvsl[$dong][$cot] = $kqbh[$keyct][$keylk];
							$tong +=$datadvsl[$dong][$cot];
						}
						elseif($keylk=='tong')
						{
							$datadvsl[$dong][$cot] = $tong;
						}
						elseif($keylk=='kehoach')
						{
							$datadvsl[$dong][$cot] = $kehoach[$valuect];
						}
						else
						{
							$datadvsl[$dong][$cot] = $tong*100/$kehoach[$valuect].'%';
						}
						$cot++;
					endforeach;
					$dong++;				
				endforeach;
	    	}
			$this->set(compact('loaikenhdvs','chitieudvs','datadvsl','donvi','ngaybd','ngaykt','donvi_id'));
		}
        $this->set(compact('ngaybd','ngaykt','donvi_id'));
	}
	
    function laykqbhexcel($donvi_id=0)
	{
		//	$ngaybd = $this->data['Kqbanhangtt']['ngaybd'];
	//	$ngaykt = $this->data['Kqbanhangtt']['ngaykt'];
		if (date('m',strtotime($ngaybd))!=date('m',strtotime($ngaykt)))
		{
			echo "<h2>ngày bắt đầu và ngày kết thúc phải cùng Tháng</h2>";
		}
		else
		{
			$donvi = $this->Kqbanhangtt->Chitieudv->Donvi->find('all',array('conditions'=>array('id'=>$donvi_id)));
			$ngaybd=date('Y-m-d',strtotime($ngaybd));
			$ngaykt=date('Y-m-d',strtotime($ngaykt));
			$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>$ngaybd, 'ngaykt >='=>$ngaykt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
			$chitieus = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id')));
    		$kqbh = $this->Kqbanhangtt->find('list',array(
    												'conditions'=>array('chitieudv_id'=>$chitieus,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    												'fields'=>array('loaikenh_id','soluong','chitieudv_id')));
    		$chitieudvs = $this->Chitieudv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    		$kenhs = $this->Phankenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Nhanvien'),
    											'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
    											'fields'=>array('Phankenh.kenh_id')
    												));
    		$loaikenhdvs = $this->Kenh->find('list',array(
    												'recursive'=>0,'contain'=>array('Loaikenh'),
    												'conditions'=>array('Kenh.id'=>$kenhs),
    												'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    		
    		$kehoach = $this->Chitieudv->find('list',array(
    										'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    										'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')
    												));
	    	if (!empty($kqbh))
	    	{
		    	$cot=0;
				$dong=0;
				$loaikenhdvs['tong']='Tổng';
				$loaikenhdvs['kehoach']='Kế Hoạch';
				$loaikenhdvs['hoanthanh']='% Hoàn Thành';
				foreach ($chitieudvs as $keyct=>$valuect):
					$cot=0;
					$tong=0;
					foreach ($loaikenhdvs as $keylk=>$valuelk):
						if($keylk!='tong' && $keylk!='kehoach' && $keylk!='hoanthanh')
						{
							$datadvsl[$dong][$cot] = $kqbh[$keyct][$keylk];
							$tong +=$datadvsl[$dong][$cot];
						}
						elseif($keylk=='tong')
						{
							$datadvsl[$dong][$cot] = $tong;
						}
						elseif($keylk=='kehoach')
						{
							$datadvsl[$dong][$cot] = $kehoach[$valuect];
						}
						else
						{
							$datadvsl[$dong][$cot] = $tong*100/$kehoach[$valuect].'%';
						}
						$cot++;
					endforeach;
					$dong++;				
				endforeach;
	    	}
			$this->set(compact('loaikenhdvs','chitieudvs','datadvsl','donvi','ngaybd','ngaykt','donvi_id'));
		}
        $this->set(compact('ngaybd','ngaykt','donvi_id'));
        $this->layout='';
	
	}
	
	function kqbanhangthangttexcel($ngaybd,$ngaykt,$donvi_id=0)
	{
		$this->layout='ajax';
		$ngaybd = $this->data['Kqbanhangtt']['ngaybd'];
		$ngaykt = $this->data['Kqbanhangtt']['ngaykt'];
		if (date('m',strtotime($ngaybd))!=date('m',strtotime($ngaykt)))
		{
			echo "<h2>ngày bắt đầu và ngày kết thúc phải cùng Tháng</h2>";
		}
		else
		{
			$donvi = $this->Kqbanhangtt->Chitieudv->Donvi->find('all',array('conditions'=>array('id'=>$donvi_id)));
			$ngaybd=date('Y-m-d',strtotime($ngaybd));
			$ngaykt=date('Y-m-d',strtotime($ngaykt));
			$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>$ngaybd, 'ngaykt >='=>$ngaykt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
			$chitieus = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id')));
    		$kqbh = $this->Kqbanhangtt->find('list',array(
    												'conditions'=>array('chitieudv_id'=>$chitieus,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    												'fields'=>array('loaikenh_id','soluong','chitieudv_id')));
    		$chitieudvs = $this->Chitieudv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    		$kenhs = $this->Phankenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Nhanvien'),
    											'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
    											'fields'=>array('Phankenh.kenh_id')
    												));
    		$loaikenhdvs = $this->Kenh->find('list',array(
    												'recursive'=>0,'contain'=>array('Loaikenh'),
    												'conditions'=>array('Kenh.id'=>$kenhs),
    												'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    		
    		$kehoach = $this->Chitieudv->find('list',array(
    										'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    										'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')
    												));
	    	if (!empty($kqbh))
	    	{
		    	$cot=0;
				$dong=0;
				$loaikenhdvs['tong']='Tổng';
				$loaikenhdvs['kehoach']='Kế Hoạch';
				foreach ($chitieudvs as $keyct=>$valuect):
					$cot=0;
					$tong=0;
					foreach ($loaikenhdvs as $keylk=>$valuelk):
						if($keylk!='tong' && $keylk!='kehoach')
						{
							$datadvsl[$dong][$cot] = $kqbh[$keyct][$keylk];
							$tong +=$datadvsl[$dong][$cot];
						}
						else
						{
							$datadvsl[$dong][$cot] = $tong;
						}
	    				if($keylk=='kehoach')
						{
							$datadvsl[$dong][$cot] = $kehoach[$valuect];
						}
						$cot++;
					endforeach;
					$dong++;				
				endforeach;
	    	}
			$this->set(compact('loaikenhdvs','chitieudvs','datadvsl','donvi'));
		}
	}
	
    function laykqbh_2($donvi_id=0)
	{
		$this->layout='ajax';
		$donvi_id=13;
		$ngaybd = $this->data['Kqbanhangtt']['ngaybd'];
		$ngaykt = $this->data['Kqbanhangtt']['ngaykt'];
		if (date('m',strtotime($ngaybd))!=date('m',strtotime($ngaykt)))
		{
			echo "<h2>ngày bắt đầu và ngày kết thúc phải cùng Tháng</h2>";
		}
		else
		{
			$ngaybd=date('Y-m-d',strtotime($ngaybd));
			$ngaykt=date('Y-m-d',strtotime($ngaykt));
			$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>$ngaybd, 'ngaykt >='=>$ngaykt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
			//group
			$donvis = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach','Donvi'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Kehoach.donvi_id'=>$donvi_id),
    											'fields'=>array('Chitieudv.donvi_id','Donvi.madv')));
			$chitieus = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>array_keys($donvis)),
    											'fields'=>array('Chitieudv.id','Chitieudv.donvi_id')));
			$cts = array_keys($chitieus);
    		$kqbh = $this->Kqbanhangtt->find('list',array(
    												'conditions'=>array('chitieudv_id'=>$cts,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    												'fields'=>array('loaikenh_id','soluong','chitieudv_id')));
    		$kqbh_id = $this->Kqbanhangtt->find('list',array(
    												'conditions'=>array('chitieudv_id'=>$cts,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    												'fields'=>array('loaikenh_id','soluong','chitieudv_id')));
    		$kenhs = $this->Phankenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Nhanvien'),
    											'conditions'=>array('Nhanvien.donvi_id'=>$chitieus),
    											'fields'=>array('Phankenh.kenh_id')
    												));
    		$loaikenhdvs = $this->Kenh->find('list',array(
    												'recursive'=>0,'contain'=>array('Loaikenh'),
    												'conditions'=>array('Kenh.id'=>$kenhs),
    												'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    		//row
    		$chitieudvs = $this->Chitieudv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>array_keys($donvis)),
    												'fields'=>array('Hanghoa.id','Hanghoa.tenhh')));
    		///col
    		foreach ($donvis as $keydv =>$valuedv):
    			foreach ($loaikenhdvs as $keylk =>$valuelk):
    				$col[$keydv][$keylk]=$valuelk;
    			endforeach;
    		endforeach;
    		
    		
    		
    		
    		
	    	if (!empty($kqbh))
	    	{
		    	$cot=0;
				$dong=0;
				foreach ($chitieudvs as $keyct=>$valuect):
					$cot=0;
					$tong=0;
					foreach ($loaikenhdvs as $keylk=>$valuelk):
						$datadvsl[$dong][$cot] = $kqbh[$keyct][$keylk];
						$cot++;
					endforeach;
					$dong++;				
				endforeach;
	    	}
			$this->set(compact('loaikenhdvs','chitieudvs','datadvsl'));
		}
	}

	function luykedv($donvi_id=null)
	{
		if(!$donvi_id)
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		$now = date('Y-m-d');
		$kh =$this->Chitieudv->find('first',array('conditions'=>array(
													'Chitieudv.donvi_id'=>$donvi_id,
													'Kehoach.ngaybd <='=>$now,
													'Kehoach.ngaykt >='=>$now,
													'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >='=>8)));
		$ngaybd = $kh['Kehoach']['ngaybd'];
		$ngaykt = $kh['Kehoach']['ngaykt'];
		$donvi_cha = $kh['Kehoach']['donvi_id'];
		$donvis = $this->Thuocdv->find('list',array('recursive'=>0,
													'conditions'=>array('donvi_id'=>$donvi_cha,'left(Donvi.madv,2)'=>'TT'),
													'fields'=>array('Donvi.id','Donvi.tendv')));
		$donvi = $this->Kqbanhangtt->Chitieudv->Donvi->find('all',array('conditions'=>array('id'=>$donvi_id)));
		/*
		 * Luy ke SXKD cua trung tam
		 */
		$thoigian = $this->Thoigian->find('list',array(
    								'conditions'=>array('ngaybd <='=>$ngaybd, 'ngaykt >='=>$ngaykt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    								));
		$chitieus = $this->Chitieudv->find('list',array(
    										'recursive'=>0,'contain'=>array('Kehoach'),
    										'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('id')));
		
    	$kqbhs = $this->Kqbanhangtt->find('all',array(
    											'conditions'=>array('chitieudv_id'=>$chitieus,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    											'fields'=>array('chitieudv_id','loaikenh_id','sum(Kqbanhangtt.soluong) as soluong'),
    											'group'=>array('chitieudv_id','loaikenh_id')));
    	$chitieudvs = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id','Hanghoa.tenhh')));
    	$kenhs = $this->Phankenh->find('list',array(
    										'recursive'=>0,'contain'=>array('Nhanvien'),
    										'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
    										'fields'=>array('Phankenh.kenh_id')
    										));
    	$loaikenhdvs = $this->Kenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Loaikenh'),
    											'conditions'=>array('Kenh.id'=>$kenhs),
    											'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    	
    	$kehoach = $this->Chitieudv->find('list',array(
    									'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    									'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    									'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')));
    	
	    if (!empty($kqbhs))
	    {
		   	$cot=0;
			$dong=0;
			$loaikenhdvs['tong']='Tổng';
			$loaikenhdvs['kehoach']='Kế Hoạch';
			$loaikenhdvs['hoanthanh']='% Hoàn Thành';
			$kqbh = array();
			foreach ($kqbhs as $kq)
			{
					$kqbh[$kq['Kqbanhangtt']['chitieudv_id']][$kq['Kqbanhangtt']['loaikenh_id']]=$kq[0]['soluong'];
			}
			foreach ($chitieudvs as $keyct=>$valuect):
				$cot=0;
				$tong=0;
				foreach ($loaikenhdvs as $keylk=>$valuelk):
					if($keylk!='tong' && $keylk!='kehoach' && $keylk!='hoanthanh')
					{
						$datadvsl[$dong][$cot] = isset($kqbh[$keyct][$keylk])?$kqbh[$keyct][$keylk]:0;
						$tong +=$datadvsl[$dong][$cot];
					}
					elseif($keylk=='tong')
					{
						$datadvsl[$dong][$cot] = $tong;
					}
					elseif($keylk=='kehoach')
					{
						$datadvsl[$dong][$cot] = $kehoach[$valuect];
					}
					else
					{
						
						$datadvsl[$dong][$cot] = number_format(round($tong*100/$kehoach[$valuect],2),0,',','.').'%';
					}
					$cot++;
				endforeach;
				$dong++;				
			endforeach;
	    }
		$this->set(compact('loaikenhdvs','chitieudvs','datadvsl','donvi'));
        $this->set(compact('ngaybd','ngaykt','donvi_id','donvis'));
	}
	/*
	 * tong hop bao cao trung tam
	 */
	function kqbanhangdv($donvi_id=null)
	{
		if(!$donvi_id)
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		$now = date('Y-m-d');
		$kh =$this->Chitieudv->find('first',array('conditions'=>array(
													'Chitieudv.donvi_id'=>$donvi_id,
													'Kehoach.ngaybd <='=>$now,
													'Kehoach.ngaykt >='=>$now,
													'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >='=>8)));
		$ngaybd = $kh['Kehoach']['ngaybd'];
		$ngaykt = $kh['Kehoach']['ngaykt'];
		$donvi_cha = $kh['Kehoach']['donvi_id'];
		$donvis = $this->Thuocdv->find('list',array('recursive'=>0,
													'conditions'=>array('donvi_id'=>$donvi_cha,'left(Donvi.madv,2)'=>'TT'),
													'fields'=>array('Donvi.id','Donvi.tendv')));
		$donvi = $this->Kqbanhangtt->Chitieudv->Donvi->find('all',array('conditions'=>array('id'=>$donvi_id)));
		
		
		/*
		 * Luy ke SXKD cua trung tam
		 */
		$thoigian = $this->Thoigian->find('list',array(
    								'conditions'=>array('ngaybd <='=>$ngaybd, 'ngaykt >='=>$ngaykt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    								));
		$chitieus = $this->Chitieudv->find('list',array(
    										'recursive'=>0,'contain'=>array('Kehoach'),
    										'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('id')));
		
    	$kqbhs = $this->Kqbanhangtt->find('all',array(
    											'conditions'=>array('chitieudv_id'=>$chitieus,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    											'fields'=>array('chitieudv_id','loaikenh_id','sum(Kqbanhangtt.soluong) as soluong'),
    											'group'=>array('chitieudv_id','loaikenh_id')));
    	$chitieudvs = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id','Hanghoa.tenhh')));
    	$kenhs = $this->Phankenh->find('list',array(
    										'recursive'=>0,'contain'=>array('Nhanvien'),
    										'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
    										'fields'=>array('Phankenh.kenh_id')
    										));
    	$loaikenhdvs = $this->Kenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Loaikenh'),
    											'conditions'=>array('Kenh.id'=>$kenhs),
    											'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    	
    	$kehoach = $this->Chitieudv->find('list',array(
    									'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    									'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    									'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')));
    	
	    if (!empty($kqbhs))
	    {
		   	$cot=0;
			$dong=0;
			$loaikenhdvs['tong']='Tổng';
			$loaikenhdvs['kehoach']='Kế Hoạch';
			$loaikenhdvs['hoanthanh']='% Hoàn Thành';
			$kqbh = array();
			foreach ($kqbhs as $kq)
			{
					$kqbh[$kq['Kqbanhangtt']['chitieudv_id']][$kq['Kqbanhangtt']['loaikenh_id']]=$kq[0]['soluong'];
			}
			foreach ($chitieudvs as $keyct=>$valuect):
				$cot=0;
				$tong=0;
				foreach ($loaikenhdvs as $keylk=>$valuelk):
					if($keylk!='tong' && $keylk!='kehoach' && $keylk!='hoanthanh')
					{
						$datadvsl[$dong][$cot] = (empty($kqbh[$keyct][$keylk])?0:$kqbh[$keyct][$keylk]);
						$tong +=$datadvsl[$dong][$cot];
					}
					elseif($keylk=='tong')
					{
						$datadvsl[$dong][$cot] = $tong;
					}
					elseif($keylk=='kehoach')
					{
					$datadvsl[$dong][$cot] = $kehoach[$valuect];
						}
					else
					{
						$datadvsl[$dong][$cot] =number_format(round($tong*100/$kehoach[$valuect],2),0,',','.').'%';
					}
					$cot++;
				endforeach;
				$dong++;				
			endforeach;
	    }

		$this->set(compact('loaikenhdvs','chitieudvs','datadvsl','donvi'));
        $this->set(compact('ngaybd','ngaykt','donvi_id','donvis'));
        
        /*
         * ket qua ban hang thang - tuan - ngay
         */
        $ngaybdngay = date('Y-m-d');
		$ngayktngay = date('Y-m-d');
		$thoigianngay = $this->Thoigian->find('list',array(
    								'conditions'=>array('ngaybd <='=>$ngaybdngay, 'ngaykt >='=>$ngayktngay,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    								));
		$chitieungays = $this->Chitieudv->find('list',array(
    										'recursive'=>0,'contain'=>array('Kehoach'),
    										'conditions'=>array('Kehoach.thoigian_id'=>key($thoigianngay),'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('id')));
		
    	$kqbhngays = $this->Kqbanhangtt->find('all',array(
    											'conditions'=>array('chitieudv_id'=>$chitieungays,'ngay >='=>$ngaybdngay,'ngay <='=>$ngayktngay),
    											'fields'=>array('chitieudv_id','loaikenh_id','sum(Kqbanhangtt.soluong) as soluong'),
    											'group'=>array('chitieudv_id','loaikenh_id')));
    	$chitieudvngays = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigianngay),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id','Hanghoa.tenhh')));
    	$kenhs = $this->Phankenh->find('list',array(
    										'recursive'=>0,'contain'=>array('Nhanvien'),
    										'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
    										'fields'=>array('Phankenh.kenh_id')
    										));
    	$loaikenhdvngays = $this->Kenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Loaikenh'),
    											'conditions'=>array('Kenh.id'=>$kenhs),
    											'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    	
    	$kehoach = $this->Chitieudv->find('list',array(
    									'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    									'conditions'=>array('Kehoach.thoigian_id'=>key($thoigianngay),'Chitieudv.donvi_id'=>$donvi_id),
    									'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')));
    	
	    if (!empty($kqbhngays))
	    {
		   	$cot=0;
			$dong=0;
			$loaikenhdvngays['tong']='Tổng';
			$loaikenhdvngays['kehoach']='Kế Hoạch';
			$loaikenhdvngays['hoanthanh']='% Hoàn Thành';
			$kqbh = array();
			foreach ($kqbhngays as $kq)
			{
					$kqbh[$kq['Kqbanhangtt']['chitieudv_id']][$kq['Kqbanhangtt']['loaikenh_id']]=$kq[0]['soluong'];
			}
			foreach ($chitieudvngays as $keyct=>$valuect):
				$cot=0;
				$tong=0;
				foreach ($loaikenhdvs as $keylk=>$valuelk):
					if($keylk!='tong' && $keylk!='kehoach' && $keylk!='hoanthanh')
					{
						$datadvslngay[$dong][$cot] = isset($kqbh[$keyct][$keylk])?$kqbh[$keyct][$keylk]:0;
						$tong +=(isset($datadvslngay[$dong][$cot])?$datadvslngay[$dong][$cot]:0);
					}
					elseif($keylk=='tong')
					{
						$datadvslngay[$dong][$cot] = $tong;
					}
					elseif($keylk=='kehoach')
					{
					$datadvslngay[$dong][$cot] = $kehoach[$valuect];
						}
					else
					{
						$datadvslngay[$dong][$cot] = round($tong*100/$kehoach[$valuect],2).'%';
					}
					$cot++;
				endforeach;
				$dong++;				
			endforeach;
	    }
		$this->set(compact('loaikenhdvngays','chitieudvngays','datadvslngay','ngaybdngay','ngayktngay'));
		/*
		 * ket qua ban hang luu dong
		 */
		/*$nhanvientt = $this->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																	'fields'=>array('id','tennv')));
		$nhanvien_id = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));
		$ngaybdbhld = date('Y-m-d');
		$ngayktbhld  = date('Y-m-d');
		$tgbhld = $this->Chitieunv->find('first',array('recursive'=>0,
											'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																'Kehoach.ngaybd <='=>$ngaybdbhld,
																'Kehoach.ngaykt >='=>$ngayktbhld,
																'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8),
											'fields'=>array('Kehoach.id','Kehoach.ngaybd','Kehoach.ngaykt')));
		$khbhld = $tgbhld['Kehoach']['id'];
		$this->Kehoachbhld->recursive = 1;
		$this->paginate['Kehoachbhld'] = array('order'=>array('thoigian DESC'),
								'conditions'=>array('Kehoachbhld.nhanvien_id'=>$nhanvien_id,
																	'thoigian >='=>$ngaybdbhld,
																	'thoigian <='=>$ngayktbhld));
		$this->set('chitieungaybhlds', $this->paginate('Kehoachbhld'));
		$hanghoabhlds = $this->Chitieunv->find('list',array('recursive'=>0,
												'conditions'=>array('kehoach_id'=>$khbhld,
																	'Chitieunv.nhanvien_id'=>$nhanvien_id),
												'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),'order'=>array('Hanghoa.id DESC')));
		$this->set(compact('hanghoabhlds','nhanvientt'));*/
		/*
		 * luy ke tong hop nhanvien
		 */
		$nvkh = $this->Zluykenv->find('list',array(
												'recursive'=>0,
												'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
												'fields'=>array('nhanvien_id','nhanvien_id')));
			$nhanviens = $this->Chitieunv->Nhanvien->find('list',array('conditions'=>array('id'=>$nvkh),'fields'=>array('tennv')));
			/*
			 * set col luy ke
			 */
			$i=0;
			foreach ($nhanviens as $keynv =>$valuenv):
				$cols[$keynv][$keynv.'0']="Lũy Kế Tuần N";
	    		$cols[$keynv][$keynv.'1']="Kế Hoạch Tuần N";
	    		$cols[$keynv][$keynv.'2']="% Hoàn Thành Tuần";
	    		$cols[$keynv][$keynv.'3']="Kết Quả Tuần N-1";
	    		$cols[$keynv][$keynv.'4']="Lũy Kế Tháng";
	    		$cols[$keynv][$keynv.'5']="Kế Hoạch Tháng";
	    		$cols[$keynv][$keynv.'6']="% Hoàn Thành Tháng";
			endforeach;
			/*
			 * lay data
			 */
			$data =$this->Zluykenv->find('all',array('conditions'=>array('nhanvien_id'=>array_keys($nhanviens),)));
			if(!empty($data))
			{
			/*
			 * sap xep lai row hang hoa
			 */
			for ($i=0;$i<count($data);$i++)
	    	{
	    		$rows[$data[$i]['Hanghoa']['id']]=$data[$i]['Hanghoa']['tenhh'];
	    	}
	    	
	    	/*
			 * sap xep lai data
			 */
	    	$i=0;
	    	$tong =array();
	    	foreach ($nhanviens as $keynv =>$valuenv):
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['nhanvien_id']==$keynv && $data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
		    				$luyketh[$keyhh][$keynv.'0']=$data[$j]['Zluykenv']['tuanN'];
		    				$luyketh[$keyhh][$keynv.'1']=$data[$j]['Zluykenv']['khtuan'];
		    				$luyketh[$keyhh][$keynv.'2']=round($data[$j]['Zluykenv']['hoanthanhtuan']).'%';
		    				$luyketh[$keyhh][$keynv.'3']=$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'4']=$data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'5']=$data[$j]['Zluykenv']['khthang'];
		    				$luyketh[$keyhh][$keynv.'6']=round($data[$j]['Zluykenv']['hoanthanhthang']).'%';
		    			}
		    		}
		    		
		    	}
	    	endforeach;
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
							$tong[$keyhh]=$tong[$keyhh]+ $data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];		    				
		    			}
		    		}
		    		
		    	}
			}
			foreach ($nhanviens as $key => $value)
				$nhanviens[$key] = '<a href="/kqbanhangs/kqbanhangnv/'.$key.'">'.$value .'</a>';
	    	$this->set(compact('cols','rows','nhanviens','luyketh','tong','donvi_id'));
	    
	   			/*
				 *	thong ke so luong san pham theo kenh 
				 */
				/*$khtk = $this->Kehoach->find('first',array('conditions'=>array('donvi_id'=>$donvi_id,
																				'Kehoach.ngaybd <='=>date('Y-m-d'),
																				'Kehoach.ngaykt >='=>date('Y-m-d'),
																				'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8)));
				$ngaybdtk = $khtk['Kehoach']['ngaybd'];
				$ngaykttk = $khtk['Kehoach']['ngaykt'];
				
				
				$hhtks = $this->Chitieunv->find('list',array(
															'recursive'=>0,
															'conditions'=>array('nhanvien_id'=>$nhanvien_id),
															'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),
															'group'=>array('Hanghoa.id'),
															'order'=>array('Hanghoa.tenhh')));
				/*$result = $this->Zthbhkenh->find('all',array(
												'conditions'=>array('Zthbhkenh.ngay >='=>$ngaybdtk,
																	'Zthbhkenh.ngay <='=>$ngaykttk,
																	'Zthbhkenh.nhanvien_id'=>$nhanvien_id,
																	'Zthbhkenh.hanghoa_id'=>array_keys($hhtks)),
												'fields'=>array('Zthbhkenh.kenh_id','Zthbhkenh.tenkenh','Zthbhkenh.hanghoa_id','Zthbhkenh.tenhh','sum(Zthbhkenh.soluong) as soluong'),
												'group'=>array('Zthbhkenh.hanghoa_id','Zthbhkenh.kenh_id')));*/
				
				/*$kenhtks = $this->Phankenh->find('list',array(
														'recursive'=>0,
														'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																			'ngaykt_hd'=>null),
														'fields'=>array('Kenh.id','Kenh.tenkenh')));
				$datatk = array();
				$i = 0;
				$this->set(compact('ngaybdtk','ngaykttk','kenhtks','hhtks','datatk'));*/
	}
	
	function layluykett()
	{
		$this->layout = 'ajax';
		$now = date('Y-m-d');
		$donvi_id = $this->data['Kqbanhangtt']['donvi_id'];
		$kh =$this->Chitieudv->find('first',array('conditions'=>array(
													'Chitieudv.donvi_id'=>$donvi_id,
													'Kehoach.ngaybd <='=>$now,
													'Kehoach.ngaykt >='=>$now,
													'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >='=>8)));
		$ngaybd = $kh['Kehoach']['ngaybd'];
		$ngaykt = $kh['Kehoach']['ngaykt'];
		$donvi_cha = $kh['Kehoach']['donvi_id'];
		$donvi = $this->Kqbanhangtt->Chitieudv->Donvi->find('all',array('conditions'=>array('id'=>$donvi_id)));
		$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>$ngaybd, 'ngaykt >='=>$ngaykt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
		$chitieus = $this->Chitieudv->find('list',array(
    										'recursive'=>0,'contain'=>array('Kehoach'),
    										'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('id')));
		
    	$kqbhs = $this->Kqbanhangtt->find('all',array(
    											'conditions'=>array('chitieudv_id'=>$chitieus,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    											'fields'=>array('chitieudv_id','loaikenh_id','sum(Kqbanhangtt.soluong) as soluong'),
    											'group'=>array('chitieudv_id','loaikenh_id')));
    	$chitieudvs = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id','Hanghoa.tenhh')));
    	$kenhs = $this->Phankenh->find('list',array(
    										'recursive'=>0,'contain'=>array('Nhanvien'),
    										'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
    										'fields'=>array('Phankenh.kenh_id')
    											));
    	$loaikenhdvs = $this->Kenh->find('list',array(
    												'recursive'=>0,'contain'=>array('Loaikenh'),
    											'conditions'=>array('Kenh.id'=>$kenhs),
    											'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    	
    	$kehoach = $this->Chitieudv->find('list',array(
    									'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    									'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    									'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')));
    	
	    if (!empty($kqbhs))
	   	{
		   	$cot=0;
			$dong=0;
			$loaikenhdvs['tong']='Tổng';
			$loaikenhdvs['kehoach']='Kế Hoạch';
			$loaikenhdvs['hoanthanh']='% Hoàn Thành';
			$kqbh = array();
			foreach ($kqbhs as $kq)
				$kqbh[$kq['Kqbanhangtt']['chitieudv_id']][$kq['Kqbanhangtt']['loaikenh_id']]=$kq[0]['soluong'];
				
			foreach ($chitieudvs as $keyct=>$valuect):
				$cot=0;
				$tong=0;
				foreach ($loaikenhdvs as $keylk=>$valuelk):
					if($keylk!='tong' && $keylk!='kehoach' && $keylk!='hoanthanh')
					{
						$datadvsl[$dong][$cot] = isset($kqbh[$keyct][$keylk])?$kqbh[$keyct][$keylk]:0;
						$tong +=$datadvsl[$dong][$cot];
					}
					elseif($keylk=='tong')
						$datadvsl[$dong][$cot] = $tong;
					elseif($keylk=='kehoach')
						$datadvsl[$dong][$cot] = $kehoach[$valuect];
					else
						$datadvsl[$dong][$cot] = round($tong*100/$kehoach[$valuect],2).'%';
					$cot++;
				endforeach;
				$dong++;				
				endforeach;
	    }
		$this->set(compact('loaikenhdvs','chitieudvs','datadvsl','donvi','ngaybd','ngaykt','donvi_id'));
	}


	function layluykettexcel($donvi_id)
	{
	    $tendv=$this->Zdv->find('first',array(
            'conditions'=>array('donvi_id'=>$donvi_id),
            'fields'=>array('tendvcha','tendv')));
        $this->set(compact('tendv'));
        
		$this->layout = '';
		$now = date('Y-m-d');
		//$donvi_id = $this->data['Kqbanhangtt']['donvi_id'];
		$kh =$this->Chitieudv->find('first',array('conditions'=>array(
													'Chitieudv.donvi_id'=>$donvi_id,
													'Kehoach.ngaybd <='=>$now,
													'Kehoach.ngaykt >='=>$now,
													'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >='=>8)));
		$ngaybd = $kh['Kehoach']['ngaybd'];
		$ngaykt = $kh['Kehoach']['ngaykt'];
		$donvi_cha = $kh['Kehoach']['donvi_id'];
		$donvi = $this->Kqbanhangtt->Chitieudv->Donvi->find('all',array('conditions'=>array('id'=>$donvi_id)));
		$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>$ngaybd, 'ngaykt >='=>$ngaykt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
		$chitieus = $this->Chitieudv->find('list',array(
    										'recursive'=>0,'contain'=>array('Kehoach'),
    										'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('id')));
		
    	$kqbhs = $this->Kqbanhangtt->find('all',array(
    											'conditions'=>array('chitieudv_id'=>$chitieus,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    											'fields'=>array('chitieudv_id','loaikenh_id','sum(Kqbanhangtt.soluong) as soluong'),
    											'group'=>array('chitieudv_id','loaikenh_id')));
    	$chitieudvs = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id','Hanghoa.tenhh')));
    	$kenhs = $this->Phankenh->find('list',array(
    										'recursive'=>0,'contain'=>array('Nhanvien'),
    										'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
    										'fields'=>array('Phankenh.kenh_id')
    											));
    	$loaikenhdvs = $this->Kenh->find('list',array(
    												'recursive'=>0,'contain'=>array('Loaikenh'),
    											'conditions'=>array('Kenh.id'=>$kenhs),
    											'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    	
    	$kehoach = $this->Chitieudv->find('list',array(
    									'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    									'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    									'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')));
    	
	    if (!empty($kqbhs))
	   	{
		   	$cot=0;
			$dong=0;
			$loaikenhdvs['tong']='Tổng';
			$loaikenhdvs['kehoach']='Kế Hoạch';
			$loaikenhdvs['hoanthanh']='% Hoàn Thành';
			$kqbh = array();
			foreach ($kqbhs as $kq)
				$kqbh[$kq['Kqbanhangtt']['chitieudv_id']][$kq['Kqbanhangtt']['loaikenh_id']]=$kq[0]['soluong'];
				
			foreach ($chitieudvs as $keyct=>$valuect):
				$cot=0;
				$tong=0;
				foreach ($loaikenhdvs as $keylk=>$valuelk):
					if($keylk!='tong' && $keylk!='kehoach' && $keylk!='hoanthanh')
					{
						$datadvsl[$dong][$cot] = isset($kqbh[$keyct][$keylk])?$kqbh[$keyct][$keylk]:0;
						$tong +=$datadvsl[$dong][$cot];
					}
					elseif($keylk=='tong')
						$datadvsl[$dong][$cot] = $tong;
					elseif($keylk=='kehoach')
						$datadvsl[$dong][$cot] = $kehoach[$valuect];
					else
						$datadvsl[$dong][$cot] = round($tong*100/$kehoach[$valuect],2).'%';
					$cot++;
				endforeach;
				$dong++;				
				endforeach;
	    }
		$this->set(compact('loaikenhdvs','chitieudvs','datadvsl','donvi','ngaybd','ngaykt','donvi_id'));
	}


	function laykqbhtt()
	{
		$this->layout='ajax';
		$donvi_id = $this->data['Kqbanhangtt']['donvi_id'];
		$ngaybd = $this->data['Kqbanhangtt']['ngaybd'];
		$ngaykt = $this->data['Kqbanhangtt']['ngaykt'];
		if (date('m',strtotime($ngaybd))!=date('m',strtotime($ngaykt)))
		{
			echo "<h2>ngày bắt đầu và ngày kết thúc phải cùng Tháng</h2>";
		}
		else
		{
			$donvi = $this->Kqbanhangtt->Chitieudv->Donvi->find('all',array('conditions'=>array('id'=>$donvi_id)));
			$ngaybd=date('Y-m-d',strtotime($ngaybd));
			$ngaykt=date('Y-m-d',strtotime($ngaykt));
			$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>$ngaybd, 'ngaykt >='=>$ngaykt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
			$chitieus = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id')));
			
    		$kqbhs = $this->Kqbanhangtt->find('all',array(
    												'conditions'=>array('chitieudv_id'=>$chitieus,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    												'fields'=>array('chitieudv_id','loaikenh_id','sum(Kqbanhangtt.soluong) as soluong'),
    												'group'=>array('chitieudv_id','loaikenh_id')));
    		$chitieudvs = $this->Chitieudv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    		$kenhs = $this->Phankenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Nhanvien'),
    											'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
    											'fields'=>array('Phankenh.kenh_id')
    												));
    		$loaikenhdvs = $this->Kenh->find('list',array(
    												'recursive'=>0,'contain'=>array('Loaikenh'),
    												'conditions'=>array('Kenh.id'=>$kenhs),
    												'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    		
    		$kehoach = $this->Chitieudv->find('list',array(
    										'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    										'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')));
    		
	    	if (!empty($kqbhs))
	    	{
		    	$cot=0;
				$dong=0;
				$loaikenhdvs['tong']='Tổng';
				$loaikenhdvs['kehoach']='Kế Hoạch';
				$loaikenhdvs['hoanthanh']='% Hoàn Thành';
				$kqbh = array();
				foreach ($kqbhs as $kq)
				{
					$kqbh[$kq['Kqbanhangtt']['chitieudv_id']][$kq['Kqbanhangtt']['loaikenh_id']]=$kq[0]['soluong'];
				}
				foreach ($chitieudvs as $keyct=>$valuect):
					$cot=0;
					$tong=0;
					foreach ($loaikenhdvs as $keylk=>$valuelk):
						if($keylk!='tong' && $keylk!='kehoach' && $keylk!='hoanthanh')
						{
							$datadvsl[$dong][$cot] = empty($kqbh[$keyct][$keylk])?0:$kqbh[$keyct][$keylk];
							$tong +=$datadvsl[$dong][$cot];
						}
						elseif($keylk=='tong')
						{
							$datadvsl[$dong][$cot] = $tong;
						}
						elseif($keylk=='kehoach')
						{
							$datadvsl[$dong][$cot] = $kehoach[$valuect];
						}
						else
						{
							$datadvsl[$dong][$cot] = number_format(round($tong*100/$kehoach[$valuect],2),0,',','.').'%';
						}
						$cot++;
					endforeach;
					$dong++;				
				endforeach;
	    	}
			$this->set(compact('loaikenhdvs','chitieudvs','datadvsl','donvi','ngaybd','ngaykt','donvi_id'));
		}
        $this->set(compact('ngaybd','ngaykt','donvi_id'));
	}


    function laykqbhttexcel($donvi_id,$ngaybd,$ngaykt)
	{
	    $tendv=$this->Zdv->find('first',array(
            'conditions'=>array('donvi_id'=>$donvi_id),
            'fields'=>array('tendvcha','tendv')));
        $this->set(compact('tendv'));
		$this->layout='';
		//$donvi_id = $this->data['Kqbanhangtt']['donvi_id'];
//		$ngaybd = $this->data['Kqbanhangtt']['ngaybd'];
//		$ngaykt = $this->data['Kqbanhangtt']['ngaykt'];
		if (date('m',strtotime($ngaybd))!=date('m',strtotime($ngaykt)))
		{
			echo "<h2>ngày bắt đầu và ngày kết thúc phải cùng Tháng</h2>";
		}
		else
		{
			$donvi = $this->Kqbanhangtt->Chitieudv->Donvi->find('all',array('conditions'=>array('id'=>$donvi_id)));
			$ngaybd=date('Y-m-d',strtotime($ngaybd));
			$ngaykt=date('Y-m-d',strtotime($ngaykt));
			$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>$ngaybd, 'ngaykt >='=>$ngaykt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
			$chitieus = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id')));
			
    		$kqbhs = $this->Kqbanhangtt->find('all',array(
    												'conditions'=>array('chitieudv_id'=>$chitieus,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    												'fields'=>array('chitieudv_id','loaikenh_id','sum(Kqbanhangtt.soluong) as soluong'),
    												'group'=>array('chitieudv_id','loaikenh_id')));
    		$chitieudvs = $this->Chitieudv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    		$kenhs = $this->Phankenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Nhanvien'),
    											'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
    											'fields'=>array('Phankenh.kenh_id')
    												));
    		$loaikenhdvs = $this->Kenh->find('list',array(
    												'recursive'=>0,'contain'=>array('Loaikenh'),
    												'conditions'=>array('Kenh.id'=>$kenhs),
    												'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    		
    		$kehoach = $this->Chitieudv->find('list',array(
    										'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    										'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')));
    		
	    	if (!empty($kqbhs))
	    	{
		    	$cot=0;
				$dong=0;
				$loaikenhdvs['tong']='Tổng';
				$loaikenhdvs['kehoach']='Kế Hoạch';
				$loaikenhdvs['hoanthanh']='% Hoàn Thành';
				$kqbh = array();
				foreach ($kqbhs as $kq)
				{
					$kqbh[$kq['Kqbanhangtt']['chitieudv_id']][$kq['Kqbanhangtt']['loaikenh_id']]=$kq[0]['soluong'];
				}
				foreach ($chitieudvs as $keyct=>$valuect):
					$cot=0;
					$tong=0;
					foreach ($loaikenhdvs as $keylk=>$valuelk):
						if($keylk!='tong' && $keylk!='kehoach' && $keylk!='hoanthanh')
						{
							$datadvsl[$dong][$cot] = empty($kqbh[$keyct][$keylk])?0:$kqbh[$keyct][$keylk];
							$tong +=$datadvsl[$dong][$cot];
						}
						elseif($keylk=='tong')
						{
							$datadvsl[$dong][$cot] = $tong;
						}
						elseif($keylk=='kehoach')
						{
							$datadvsl[$dong][$cot] = $kehoach[$valuect];
						}
						else
						{
							$datadvsl[$dong][$cot] = number_format(round($tong*100/$kehoach[$valuect],2),0,',','.').'%';
						}
						$cot++;
					endforeach;
					$dong++;				
				endforeach;
	    	}
			$this->set(compact('loaikenhdvs','chitieudvs','datadvsl','donvi','ngaybd','ngaykt','donvi_id'));
		}
        $this->set(compact('ngaybd','ngaykt','donvi_id'));
	}
	
	function layketqua()
	{
		$this->layout='ajax';
		$donvi_id = $this->data['Kqbanhangtt']['donvi_id'];
		if(!empty($this->data['Kqbanhangtt']['nhanvientt_id']))
			$nhanvien_id = $this->data['Kqbanhangtt']['nhanvientt_id'];//$this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));
		else
			$nhanvien_id = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));
		$ngaybdbhld = date('Y-m-d',strtotime($this->data['Kqbanhangtt']['ngaybdbhld']));
		$ngayktbhld  =  date('Y-m-d',strtotime($this->data['Kqbanhangtt']['ngayktbhld']));
		$tgbhld = $this->Chitieunv->find('first',array('recursive'=>0,
											'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																'Kehoach.ngaybd <='=>$ngaybdbhld,
																'Kehoach.ngaykt >='=>$ngayktbhld,
																'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8),
											'fields'=>array('Kehoach.id','Kehoach.ngaybd','Kehoach.ngaykt')));
		$khbhld = $tgbhld['Kehoach']['id'];
		$this->Kehoachbhld->recursive = 1;
		$this->paginate['Kehoachbhld'] = array('order'=>array('thoigian DESC'),
								'conditions'=>array('Kehoachbhld.nhanvien_id'=>$nhanvien_id,
																	'thoigian >='=>$ngaybdbhld,
																	'thoigian <='=>$ngayktbhld));
		$this->set('chitieungaybhlds', $this->paginate('Kehoachbhld'));
		$hanghoabhlds = $this->Chitieunv->find('list',array('recursive'=>0,
												'conditions'=>array('kehoach_id'=>$khbhld,
																	'Chitieunv.nhanvien_id'=>$nhanvien_id),
												'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),'order'=>array('Hanghoa.id DESC')));

		$this->set(compact('hanghoabhlds'));
	}  
	
	function luyketonghopnv($donvi_id=null)
	{
		$this->layout = 'ajax';
			$donvi_id = $this->data['Kqbanhangtt']['donvi_id'];
			$nvkh = $this->Zluykenv->find('list',array(
												'recursive'=>0,
												'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
												'fields'=>array('nhanvien_id','nhanvien_id')));
			$nhanviens = $this->Chitieunv->Nhanvien->find('list',array('conditions'=>array('id'=>$nvkh),'fields'=>array('tennv')));
			/*
			 * set col luy ke
			 */
			$i=0;
			foreach ($nhanviens as $keynv =>$valuenv):
				$cols[$keynv][$keynv.'0']="Lũy Kế Tuần N";
	    		$cols[$keynv][$keynv.'1']="Kế Hoạch Tuần N";
	    		$cols[$keynv][$keynv.'2']="% Hoàn Thành Tuần";
	    		$cols[$keynv][$keynv.'3']="Kết Quả Tuần N-1";
	    		$cols[$keynv][$keynv.'4']="Lũy Kế Tháng";
	    		$cols[$keynv][$keynv.'5']="Kế Hoạch Tháng";
	    		$cols[$keynv][$keynv.'6']="% Hoàn Thành Tháng";
			endforeach;
			/*
			 * lay data
			 */
			$data =$this->Zluykenv->find('all',array('conditions'=>array('nhanvien_id'=>array_keys($nhanviens),)));
			if(!empty($data))
			{
			/*
			 * sap xep lai row hang hoa
			 */
			for ($i=0;$i<count($data);$i++)
	    	{
	    		$rows[$data[$i]['Hanghoa']['id']]=$data[$i]['Hanghoa']['tenhh'];
	    	}
	    	
	    	/*
			 * sap xep lai data
			 */
	    	$i=0;
	    	$tong =array();
	    	foreach ($nhanviens as $keynv =>$valuenv):
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['nhanvien_id']==$keynv && $data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
		    				$luyketh[$keyhh][$keynv.'0']=$data[$j]['Zluykenv']['tuanN'];
		    				$luyketh[$keyhh][$keynv.'1']=$data[$j]['Zluykenv']['khtuan'];
		    				$luyketh[$keyhh][$keynv.'2']=round($data[$j]['Zluykenv']['hoanthanhtuan']).'%';
		    				$luyketh[$keyhh][$keynv.'3']=$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'4']=$data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'5']=$data[$j]['Zluykenv']['khthang'];
		    				$luyketh[$keyhh][$keynv.'6']=round($data[$j]['Zluykenv']['hoanthanhthang']).'%';
		    			}
		    		}
		    		
		    	}
	    	endforeach;
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
							$tong[$keyhh]=$tong[$keyhh]+ $data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];		    				
		    			}
		    		}
		    		
		    	}
			}
			foreach ($nhanviens as $key => $value)
				$nhanviens[$key] = '<a href="/kqbanhangs/kqbanhangnv/'.$key.'">'.$value .'</a>';
	    	$this->set(compact('cols','rows','nhanviens','luyketh','tong','donvi_id'));

	}
	
    function luyketonghopnvexcel($donvi_id)
	{
		$this->layout = '';
			//$donvi_id = $this->data['Kqbanhangtt']['donvi_id'];
			$nvkh = $this->Zluykenv->find('list',array(
												'recursive'=>0,
												'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
												'fields'=>array('nhanvien_id','nhanvien_id')));
			$nhanviens = $this->Chitieunv->Nhanvien->find('list',
                array('conditions'=>array('id'=>$nvkh),'fields'=>array('tennv')));
			/*
			 * set col luy ke
			 */
			$i=0;
			foreach ($nhanviens as $keynv =>$valuenv):
				$cols[$keynv][$keynv.'0']="Lũy Kế Tuần N";
	    		$cols[$keynv][$keynv.'1']="Kế Hoạch Tuần N";
	    		$cols[$keynv][$keynv.'2']="% Hoàn Thành Tuần";
	    		$cols[$keynv][$keynv.'3']="Kết Quả Tuần N-1";
	    		$cols[$keynv][$keynv.'4']="Lũy Kế Tháng";
	    		$cols[$keynv][$keynv.'5']="Kế Hoạch Tháng";
	    		$cols[$keynv][$keynv.'6']="% Hoàn Thành Tháng";
			endforeach;
			/*
			 * lay data
			 */
			$data =$this->Zluykenv->find('all',array('conditions'=>array('nhanvien_id'=>array_keys($nhanviens),)));
			if(!empty($data))
			{
			/*
			 * sap xep lai row hang hoa
			 */
			for ($i=0;$i<count($data);$i++)
	    	{
	    		$rows[$data[$i]['Hanghoa']['id']]=$data[$i]['Hanghoa']['tenhh'];
	    	}
	    	
	    	/*
			 * sap xep lai data
			 */
	    	$i=0;
	    	$tong =array();
	    	foreach ($nhanviens as $keynv =>$valuenv):
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['nhanvien_id']==$keynv && $data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
		    				$luyketh[$keyhh][$keynv.'0']=$data[$j]['Zluykenv']['tuanN'];
		    				$luyketh[$keyhh][$keynv.'1']=$data[$j]['Zluykenv']['khtuan'];
		    				$luyketh[$keyhh][$keynv.'2']=round($data[$j]['Zluykenv']['hoanthanhtuan']).'%';
		    				$luyketh[$keyhh][$keynv.'3']=$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'4']=$data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'5']=$data[$j]['Zluykenv']['khthang'];
		    				$luyketh[$keyhh][$keynv.'6']=round($data[$j]['Zluykenv']['hoanthanhthang']).'%';
		    			}
		    		}
		    		
		    	}
	    	endforeach;
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
							$tong[$keyhh]=$tong[$keyhh]+ $data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];		    				
		    			}
		    		}
		    		
		    	}
			}
			foreach ($nhanviens as $key => $value)
				$nhanviens[$key] = '<a href="/kqbanhangs/kqbanhangnv/'.$key.'">'.$value .'</a>';
	    	$this->set(compact('cols','rows','nhanviens','luyketh','tong','donvi_id'));

	}
	
	function laytksoluongdv()
	{
		$this->layout = 'ajax';
		$ngaybdtk = date('Y-m-d',strtotime($this->data['Kqbanhangtt']['ngaybdtksl']));
		$ngaykttk = date('Y-m-d',strtotime($this->data['Kqbanhangtt']['ngaykttksl']));
		$donvi_id = $this->data['Kqbanhangtt']['donvi_id'];
		
		if(!empty($this->data['Kqbanhangtt']['nhanvientk_id']))
		{$nhanvien_id = $this->data['Kqbanhangtt']['nhanvientk_id'];}
		else
		{$nhanvien_id = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));}
		if(empty($nhanvien_id))
		{
			$donvi_id = $this->data['Kqbanhangtt']['donvi_id'];
			
			$nhanvien_id = $this->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																	'fields'=>array('id')));
		}
		if(!empty($this->data['Kqbanhangtt']['hanghoa_id']))
		{
			$hh = $this->data['Kqbanhangtt']['hanghoa_id'];
			$hhtks = $this->Chitieunv->Hanghoa->find('list',array('conditions'=>array('id'=>$hh),
													'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),
													'order'=>array('Hanghoa.tenhh')));
		}
		else
		{
			$hhtks = $this->Chitieunv->find('list',array(
													'recursive'=>0,
													'conditions'=>array('nhanvien_id'=>$nhanvien_id),
													'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),
													'group'=>array('Hanghoa.id'),
													'order'=>array('Hanghoa.tenhh')));
		}
		
		
		if(isset($this->data['Kqbanhangtt']['kenh_id']))
		{
			if(!empty($this->data['Kqbanhangtt']['kenh_id']))
			{
				$kenh_id = $this->data['Kqbanhangtt']['kenh_id'];		
				$kenhtks = $this->Phankenh->Kenh->find('list',array(
											'conditions'=>array('id'=>$kenh_id),
											'fields'=>array('Kenh.id','Kenh.tenkenh')));	
			}
			else
			{
				
				$kenhtks = $this->Phankenh->find('list',array(
															'recursive'=>0,
															'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																				'ngaykt_hd'=>null),
															'fields'=>array('Kenh.id','Kenh.tenkenh')));
				$kenh_id=array_keys($kenhtks);
			}
		}
		else
		{
			if(!empty($this->data['kenh_id']))
			{
				$kenh_id = $this->data['kenh_id'];		
				$kenhtks = $this->Phankenh->Kenh->find('list',array(
											'conditions'=>array('id'=>$kenh_id),
											'fields'=>array('Kenh.id','Kenh.tenkenh')));	
			}
			else
			{
				
				$kenhtks = $this->Phankenh->find('list',array(
															'recursive'=>0,
															'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																				'ngaykt_hd'=>null),
															'fields'=>array('Kenh.id','Kenh.tenkenh')));
				$kenh_id=array_keys($kenhtks);
			}
		}
		
		
		$result = $this->Zthbhkenh->find('all',array(
												'conditions'=>array('Zthbhkenh.ngay >='=>$ngaybdtk,
																	'Zthbhkenh.ngay <='=>$ngaykttk,
																	'Zthbhkenh.kenh_id'=>$kenh_id,
																	'Zthbhkenh.hanghoa_id'=>array_keys($hhtks)),
												'fields'=>array('Zthbhkenh.kenh_id','Zthbhkenh.tenkenh','Zthbhkenh.hanghoa_id','Zthbhkenh.tenhh','sum(Zthbhkenh.soluong) as soluong'),
												'group'=>array('Zthbhkenh.hanghoa_id','Zthbhkenh.kenh_id')));
		
		$loaikenh = $this->Loaikenh->Kenh->find('list',array('recursive'=>0,'conditions'=>array('Kenh.id'=>$kenh_id),'fields'=>array('Kenh.id','Loaikenh.loaikenh')));
		$nhanvien = $this->Phankenh->find('list',array(
															'recursive'=>0,
															'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																				'ngaykt_hd'=>null),
															'fields'=>array('Kenh.id','Nhanvien.tennv')));
		$col_2[]='Loại kênh';
		$col_2[]='Nhân viên';
		$datatk = array();
		$data_2 = array();
		$i = 0;
		foreach ($kenhtks as $keykenh => $valuekenh)
		{
			$j=0;
			foreach ($hhtks as $keyhh =>$valuehh)
			{
				foreach ($result as  $value)
				{
					if($value['Zthbhkenh']['kenh_id']==$keykenh &&$value['Zthbhkenh']['hanghoa_id']==$keyhh)
					{
						//$datatk[$keykenh][$keyhh]=$value[0]['soluong'];
						$datatk[$i][$j]=$value[0]['soluong'];
						break;
					} 
				}
				$j++;
				$data_2[$i][0]= $loaikenh[$keykenh];
				$data_2[$i][1]= $nhanvien[$keykenh];
			}
			$i++;
		}
		$kenhtks = array_values($kenhtks);
		$this->set(compact('ngaybdtk','ngaykttk','kenhtks','hhtks','datatk','col_2','data_2'));
		//$this->set(compact('ngaybdtk','ngaykttk','kenhtks','hhtks','datatk'));
	}	
	
	function laykkenh()
	{
		$this->layout = 'ajax';
		$donvi_id = $this->data['Kqbanhangtt']['donvi_id'];
		if(isset($this->data['Kqbanhangtt']['nhanvientk_id']))
		{
			if(!empty($this->data['Kqbanhangtt']['nhanvientk_id']))
			{
				$nhanvien_id = $this->data['Kqbanhangtt']['nhanvientk_id'];
			}
			else
			{$nhanvien_id = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));}
		}else{
			if(!empty($this->data['nhanvientk_id']))
			{
				$nhanvien_id = $this->data['nhanvientk_id'];
			}
			else
			{$nhanvien_id = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));}
		}
		$kenhtks = $this->Phankenh->find('list',array(
														'recursive'=>0,
														'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																			'ngaykt_hd'=>null),
														'fields'=>array('Kenh.id','Kenh.tenkenh')));
		$this->set(compact('kenhtks'));
	}
	
	
	function laynvtk()
	{
		$this->layout = 'ajax';
		$donvi_id = $this->data['Kqbanhangtt']['donvi_id'];
		$nhanvientt = $this->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																	'fields'=>array('id','tennv')));
		$this->set('nhanvientt',$nhanvientt);
	}
	/*
	 * end tong hop trung tam
	 */
	
	/*
	 * tong hop bao cao chi nhanh
	 */
	function kqbanhangcn($donvi_id=null)
	{
		if(!$donvi_id)
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		
		/*$donvis = $this->Thuocdv->find('list',array('recursive'=>0,
													'conditions'=>array('donvi_id'=>$donvi_id,'left(Donvi.madv,2)'=>'CN'),
													'fields'=>array('Donvi.id','Donvi.tendv')));*/
		
		/*
		 * luy ke chi nhanh
		 */
		$donvi = $this->Kqbanhangtt->Chitieudv->Donvi->find('all',array('conditions'=>array('id'=>$donvi_id)));
		
		
    	$kh = $this->Chitieudv->find('first',array('conditions'=>array('Chitieudv.donvi_id'=>$donvi_id,
    												'ngaybd <='=>date('Y-m-d'),
    												'ngaykt >='=>date('Y-m-d'),
    												'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>7)));
    	$ngaybd = $kh['Kehoach']['ngaybd'];
    	$ngaykt = $kh['Kehoach']['ngaykt'];
		$chitieus = $this->Chitieudv->find('list',array(
    										'recursive'=>0,
    										'conditions'=>array('ngaybd <='=>$ngaybd,
    															'ngaykt >='=>$ngaykt,
    															'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>7,
    															'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('id')));
    	$kqbh = $this->Kqbanhangtt->find('list',array(
    											'conditions'=>array('chitieudv_id'=>$chitieus,
    											'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    											'fields'=>array('loaikenh_id','soluong','chitieudv_id')));
    	$chitieudvs = $this->Chitieudv->find('list',array(
    											'recursive'=>0,
    											'conditions'=>array('ngaybd <='=>$ngaybd,
    																'ngaykt >='=>$ngaykt,
    																'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>7,
    																'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id','Hanghoa.tenhh')));
    	$loaikenhdvs = $this->Loaikenh->find('list',array(
    											'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    	
    	$kehoach = $this->Chitieudv->find('list',array(
    									'recursive'=>0,
    									'conditions'=>array('ngaybd <='=>$ngaybd,
    														'ngaykt >='=>$ngaykt,
    														'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>7,
    														'Chitieudv.donvi_id'=>$donvi_id),
    									'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')));
	    if (!empty($kqbh))
	    {
		   	$cot=0;
			$dong=0;
			$loaikenhdvs['tong']='Tổng';
			$loaikenhdvs['kehoach']='Kế Hoạch';
			$loaikenhdvs['hoanthanh']='% Hoàn Thành';
			foreach ($chitieudvs as $keyct=>$valuect):
				$cot=0;
				$tong=0;
				foreach ($loaikenhdvs as $keylk=>$valuelk):
					if($keylk!='tong' && $keylk!='kehoach' && $keylk!='hoanthanh')
					{
						$datadvsl[$dong][$cot] = $kqbh[$keyct][$keylk];
						$tong +=$datadvsl[$dong][$cot];
					}
					elseif($keylk=='tong')
					{
						$datadvsl[$dong][$cot] = $tong;
					}
					elseif($keylk=='kehoach')
					{
						$datadvsl[$dong][$cot] = $kehoach[$valuect];
					}
					else
					{
						$datadvsl[$dong][$cot] = round($tong*100/$kehoach[$valuect],2).'%';
					}
					$cot++;
				endforeach;
				$dong++;				
			endforeach;
	    }
		$this->set(compact('loaikenhdvs','chitieudvs','datadvsl','donvi','ngaybd','ngaykt','donvi_id'));
		
		
		/*
		 * ket qua ban hang thang ngay
		 */
		$kh = $this->Chitieudv->find('first',array('conditions'=>array('Chitieudv.donvi_id'=>$donvi_id,
    												'ngaybd <='=>date('Y-m-d'),
    												'ngaykt >='=>date('Y-m-d'),
    												'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>7)));
    	$ngaybdngay = $kh['Kehoach']['ngaybd'];
    	$ngayktngay = $kh['Kehoach']['ngaykt'];
		$chitieus = $this->Chitieudv->find('list',array(
    										'recursive'=>0,
    										'conditions'=>array('ngaybd <='=>$ngaybdngay,
    															'ngaykt >='=>$ngayktngay,
    															'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>7,
    															'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('id')));
    	$kqbh = $this->Kqbanhangtt->find('list',array(
    											'conditions'=>array('chitieudv_id'=>$chitieus,
    											'ngay >='=>$ngaybdngay,'ngay <='=>$ngayktngay),
    											'fields'=>array('loaikenh_id','soluong','chitieudv_id')));
    	$chitieudvngays = $this->Chitieudv->find('list',array(
    											'recursive'=>0,
    											'conditions'=>array('ngaybd <='=>$ngaybdngay,
    																'ngaykt >='=>$ngayktngay,
    																'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>7,
    																'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id','Hanghoa.tenhh')));
    	$loaikenhdvngays = $this->Loaikenh->find('list',array(
    											'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    	
    	$kehoach = $this->Chitieudv->find('list',array(
    									'recursive'=>0,
    									'conditions'=>array('ngaybd <='=>$ngaybdngay,
    														'ngaykt >='=>$ngayktngay,
    														'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>7,
    														'Chitieudv.donvi_id'=>$donvi_id),
    									'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')));
	    if (!empty($kqbh))
	    {
		   	$cot=0;
			$dong=0;
			$loaikenhdvngays['tong']='Tổng';
			$loaikenhdvngays['kehoach']='Kế Hoạch';
			$loaikenhdvngays['hoanthanh']='% Hoàn Thành';
			foreach ($chitieudvngays as $keyct=>$valuect):
				$cot=0;
				$tong=0;
				foreach ($loaikenhdvngays as $keylk=>$valuelk):
					if($keylk!='tong' && $keylk!='kehoach' && $keylk!='hoanthanh')
					{
						$datadvslngay[$dong][$cot] = $kqbh[$keyct][$keylk];
						$tong +=$datadvslngay[$dong][$cot];
					}
					elseif($keylk=='tong')
					{
						$datadvslngay[$dong][$cot] = $tong;
					}
					elseif($keylk=='kehoach')
					{
						$datadvslngay[$dong][$cot] = $kehoach[$valuect];
					}
					else
					{
						$datadvslngay[$dong][$cot] = round($tong*100/$kehoach[$valuect],2).'%';
					}
					$cot++;
				endforeach;
				$dong++;				
			endforeach;
	    }
		$this->set(compact('loaikenhdvngays','chitieudvngays','datadvslngay','ngaybdngay','ngayktngay','donvi_id'));
		
		
		
		/*
		 * luy ke cac trung tam
		 */
		$khlk = $this->Kehoach->find('first',array('conditions'=>array('donvi_id'=>$donvi_id,
																	'Kehoach.ngaybd <='=>date('Y-m-d'),
																	'Kehoach.ngaykt >= '=>date('Y-m-d'))));
		$kehoachid = $khlk['Kehoach']['id'];
		 
		$dt =$this->Kqbanhangtt->find('all',array(
								'recursive'=>0,
								'conditions'=>array('Chitieudv.kehoach_id'=>$kehoachid),
								'fields'=>array('Chitieudv.donvi_id','Chitieudv.hanghoa_id','Chitieudv.soluong','sum(Kqbanhangtt.soluong) as soluong'),
								'group'=>array('Chitieudv.donvi_id','Chitieudv.hanghoa_id')));
		
		$dvbc = $this->Kqbanhangtt->find('list',array(
								'recursive'=>0,
								'conditions'=>array('Chitieudv.kehoach_id'=>$kehoachid),
								'fields'=>array('Chitieudv.donvi_id')));
		
		$donvilktts = $this->Donvi->find('list',array('conditions'=>array('id'=>$dvbc),'fields'=>array('madv')));
		$chitieulkdvs = $this->Chitieudv->find('list',array(
											'recursive'=>0,
											'conditions'=>array('kehoach_id'=>$kehoachid),
											'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
											'group'=>array('hanghoa_id')
												));
		$cols= array();
		foreach($donvilktts as $keydv =>$valuedv)
		{
			$cols[$keydv][$keydv.'0']='Lũy Kế';
			$cols[$keydv][$keydv.'1']='Chỉ Tiêu';
			$cols[$keydv][$keydv.'2']='%Hoàn Thành';
		}

		$i=0;
	    $tong =array();
	    foreach ($donvilktts as $keydv =>$valuedv)
	    {
			foreach ($chitieulkdvs as $keyhh =>$valuehh)
		   	{
		   		for($j=0;$j<count($dt);$j++)
		   		{
		   			if ($dt[$j]['Chitieudv']['donvi_id']==$keydv && $dt[$j]['Chitieudv']['hanghoa_id']==$keyhh)
		   			{
		   				$luyketh[$keyhh][$keydv.'0']=$dt[$j][0]['soluong'];
		   				$luyketh[$keyhh][$keydv.'1']=$dt[$j]['Chitieudv']['soluong'];
		   				$luyketh[$keyhh][$keydv.'2']=round($dt[$j][0]['soluong']*100/$dt[$j]['Chitieudv']['soluong'],2).'%';
		   			}
		   		}
		   	}
		}
		foreach ($chitieulkdvs as $keyhh =>$valuehh)
		{
		   		$tong[$keyhh]=0;
		   		for($j=0;$j<count($dt);$j++)
		   		{
		   			if ($dt[$j]['Chitieudv']['hanghoa_id']==$keyhh)
		   			{
						$tong[$keyhh]=$tong[$keyhh]+ $dt[$j][0]['soluong'];		    				
		   			}
		   		}
		   		
		}
		
		
		$this->set(compact('chitieulkdvs','tong','luyketh','cols','donvilktts'));
		
		/*
		 * ket qua ban hang luu dong
		 */
		$donvitt = $this->Thuocdv->find('list',array('recursive'=>0,
										'conditions'=>array('donvi_id'=>$donvi_id,'left(Donvi.madv,2)'=>'TT'),
										'fields'=>array('Donvi.id','Donvi.tendv')));
		
		$nhanvientt = $this->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>array_keys($donvitt)),
																	'fields'=>array('id','tennv')));
		$nhanvien_id = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>array_keys($donvitt)),'fields'=>array('id')));
		/*
		
		$ngaybdbhld = date('Y-m-d');
		$ngayktbhld  = date('Y-m-d');
		$tgbhld = $this->Chitieunv->find('first',array('recursive'=>0,
											'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																'Kehoach.ngaybd <='=>$ngaybdbhld,
																'Kehoach.ngaykt >='=>$ngayktbhld,
																'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8),
											'fields'=>array('Kehoach.id','Kehoach.ngaybd','Kehoach.ngaykt')));
		$khbhld = $tgbhld['Kehoach']['id'];
		$this->Kehoachbhld->recursive = 2;
		$this->paginate['Kehoachbhld'] = array('order'=>array('thoigian DESC'),
								'conditions'=>array('Kehoachbhld.nhanvien_id'=>$nhanvien_id,
																	'thoigian >='=>$ngaybdbhld,
																	'thoigian <='=>$ngayktbhld));
		$this->set('chitieungaybhlds', $this->paginate('Kehoachbhld'));
		$hanghoabhlds = $this->Chitieunv->find('list',array('recursive'=>0,
												'conditions'=>array('kehoach_id'=>$khbhld,
																	'Chitieunv.nhanvien_id'=>$nhanvien_id),
												'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),'order'=>array('Hanghoa.id DESC')));*/
		$this->set(compact('hanghoabhlds','nhanvientt','donvitt'));
		
		
		/*
		*	thong ke so luong san pham theo kenh 
		*/
		
		$this->Kehoach->recursive = -1;
		$khtk = $this->Kehoach->find('first',array('conditions'=>array('donvi_id'=>$donvi_id,
																		'Kehoach.ngaybd <='=>date('Y-m-d'),
																		'Kehoach.ngaykt >='=>date('Y-m-d'),
																		'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8)));
		
		$hhtks = $this->Chitieunv->find('list',array(
													'recursive'=>0,
												'conditions'=>array('nhanvien_id'=>$nhanvien_id),
													'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),
													'group'=>array('Hanghoa.id'),
													'order'=>array('Hanghoa.tenhh')));
		$datatk = array();
		$i = 0;
		
		$this->set(compact('ngaybdtk','ngaykttk','kenhtks','hhtks','datatk'));
	}	
	
	function laykqbhcn($donvi_id=0)
	{
		$this->layout='ajax';
		$ngaybd = $this->data['Kqbanhangtt']['ngaybd'];
		$ngaykt = $this->data['Kqbanhangtt']['ngaykt'];
		if (date('m',strtotime($ngaybd))!=date('m',strtotime($ngaykt)))
		{
			echo "<h2>ngày bắt đầu và ngày kết thúc phải cùng Tháng</h2>";
		}
		else
		{
			$donvi = $this->Kqbanhangtt->Chitieudv->Donvi->find('all',array('conditions'=>array('id'=>$donvi_id)));
			$ngaybd=date('Y-m-d',strtotime($ngaybd));
			$ngaykt=date('Y-m-d',strtotime($ngaykt));
			$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>$ngaybd, 'ngaykt >='=>$ngaykt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
			$chitieus = $this->Chitieudv->find('list',array(
    											'recursive'=>0,
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id')));
    		$kqbh = $this->Kqbanhangtt->find('list',array(
    												'conditions'=>array('chitieudv_id'=>$chitieus,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    												'fields'=>array('loaikenh_id','soluong','chitieudv_id')));
    		$chitieudvs = $this->Chitieudv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    		$loaikenhdvs = $this->Loaikenh->find('list',array(
    												'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    		
    		$kehoach = $this->Chitieudv->find('list',array(
    										'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    										'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')));
	    	if (!empty($kqbh))
	    	{
		    	$cot=0;
				$dong=0;
				$loaikenhdvs['tong']='Tổng';
				$loaikenhdvs['kehoach']='Kế Hoạch';
				$loaikenhdvs['hoanthanh']='% Hoàn Thành';
				foreach ($chitieudvs as $keyct=>$valuect):
					$cot=0;
					$tong=0;
					foreach ($loaikenhdvs as $keylk=>$valuelk):
						if($keylk!='tong' && $keylk!='kehoach' && $keylk!='hoanthanh')
						{
							$datadvsl[$dong][$cot] = $kqbh[$keyct][$keylk];
							$tong +=$datadvsl[$dong][$cot];
						}
						elseif($keylk=='tong')
						{
							$datadvsl[$dong][$cot] = $tong;
						}
						elseif($keylk=='kehoach')
						{
							$datadvsl[$dong][$cot] = $kehoach[$valuect];
						}
						else
						{
							$datadvsl[$dong][$cot] = $tong*100/$kehoach[$valuect].'%';
						}
						$cot++;
					endforeach;
					$dong++;				
				endforeach;
	    	}
			$this->set(compact('loaikenhdvs','chitieudvs','datadvsl','donvi','ngaybd','ngaykt','donvi_id'));
		}
        $this->set(compact('ngaybd','ngaykt','donvi_id'));
	}
	
	
	function laynv($donvi_cha)
	{
		$this->layout = 'ajax';
		$donvi_id = $this->data['Kqbanhangtt']['donvitt_id'];
		//danh cho truong hop nhiu don vi
		/*if(empty($donvi_id))
		{
			$donvi_id = $this->Thuocdv->find('list',array('recursive'=>0,'conditions'=>array('donvi_id'=>$donvi_cha,'left(Donvi.madv,2)'=>'TT'),'fields'=>array('donvi')));
		}*/
		$nhanvientt = $this->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																	'fields'=>array('id','tennv')));
		$this->set('nhanvientt',$nhanvientt);		
	}
	
	
	function layketquacn($donvi_cha)
	{
		$this->layout = 'ajax';
		/*
		 * ket qua ban hang luu dong
		 */
			if(isset($this->data['Kqbanhangtt']['nhanvientt_id']))
				$nhanvien_id = $this->data['Kqbanhangtt']['nhanvientt_id'];
			else
				$nhanvien_id = $this->data['nhanvientt_id'];

		//danh cho truong hop nhiu chi nhanh
		/*if(empty($nhanvien_id))
		{
			$donvi_id = $this->data['Kqbanhangtt']['donvitt_id'];
			if(empty($donvi_id))
			{
				$donvi_id = $this->Thuocdv->find('list',array('recursive'=>0,'conditions'=>array('donvi_id'=>$donvi_cha,'left(Donvi.madv,2)'=>'TT'),'fields'=>array('donvi')));
			}
			$nhanvien_id = $this->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																	'fields'=>array('id')));
		}*/
		$ngaybdbhld = date('Y-m-d',strtotime($this->data['Kqbanhangtt']['ngaybdbhld']));
		$ngayktbhld  = date('Y-m-d',strtotime($this->data['Kqbanhangtt']['ngayktbhld']));
		$tgbhld = $this->Chitieunv->find('first',array('recursive'=>0,
											'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																'Kehoach.ngaybd <='=>$ngaybdbhld,
																'Kehoach.ngaykt >='=>$ngayktbhld,
																'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8),
											'fields'=>array('Kehoach.id','Kehoach.ngaybd','Kehoach.ngaykt')));
		$khbhld = $tgbhld['Kehoach']['id'];
		$this->Nhanvien->unbindModel(array('belongsTo'=>array('Phankenh','Chucdanh'),'hasMany'=>array('Chitieunv','Phankenh','Nvchithi','Nvngay','Phankenh','Tempnhiemvunv','User')),false);
		$this->Kehoachbhld->recursive = 2;
		$this->paginate['Kehoachbhld'] = array('order'=>array('thoigian DESC'),
								'conditions'=>array('Kehoachbhld.nhanvien_id'=>$nhanvien_id,
																	'thoigian >='=>$ngaybdbhld,
																	'thoigian <='=>$ngayktbhld));
		
		$this->set('chitieungaybhlds', $this->paginate('Kehoachbhld'));
		
		/*$hanghoabhlds = $this->Chitieunv->find('list',array('recursive'=>0,
												'conditions'=>array('kehoach_id'=>$khbhld,
																	'Chitieunv.nhanvien_id'=>$nhanvien_id),
												'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),'order'=>array('Hanghoa.id DESC')));*/
		$hanghoabhlds = $this->Hanghoabhld->find('list',array('fields'=>array('Hanghoabhld.hanghoa_id','Hanghoabhld.tenhh')));
		$this->set(compact('hanghoabhlds','ngaybdbhld','ngayktbhld'));
	}
	/*
	 * end tong hop Chi nhanh
	 */
	
	
	
	/*
	 * chi nhanh
	 */
	function nhapkqbanhangcn()
	{
		$donvi_id = $this->Session-> read('cur_cha');
    	/*
    	 * lay chi tieu duoc giao trong thang
    	 */
    	$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>date('Y-m-d'), 'ngaykt >='=>date('Y-m-d'),'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
    	$ngay=date('Y-m-d');
    	// rows
    	$chitieudvs = $this->Chitieudv->find('list',array(
	    												'recursive'=>0,
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Kehoach.donvi_id'=>$donvi_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
		$dv = $this->Thuocdv->find('list',array(
										'conditions'=>array('donvi_id'=>$donvi_id),
										'fields'=>array('donvi','donvi')));
		$donvis = $this->Chitieudv->Donvi->find('list',array(
									'conditions'=>array('LEFT(Donvi.madv,2)'=>'TT','id'=>$dv),
										'fields'=>array('id','madv')));    	
    	
    	/*
    	 * lay kenh co bao cao trong ngay
    	 */
		$tt = key($donvis);
    	$lkngays= $this->Kqbanhangtt->find('list',array(
    										'recursive'=>0,
    										'conditions'=>array('ngay'=>$ngay,'Chitieudv.donvi_id'=>$tt),
    										'fields'=>array('loaikenh_id'),
    										'group'=>array('loaikenh_id')));
    	//cols
    	$loaikenhnvs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lkngays),
	    												'fields'=>array('id','loaikenh')));
    	$loaikenhnvs[]="Tổng";
    	$qstr='SELECT lk.id, ct.hanghoa_id,kq.ngay, sum( kq.soluong ) as soluong
				FROM kqbanhangtts kq, chitieudvs ct, loaikenhs lk, thuocdvs tdv
				WHERE kq.loaikenh_id = lk.id
					AND tdv.donvi=ct.donvi_id
					AND tdv.donvi_id= '.$donvi_id.
					' AND kq.chitieudv_id = ct.id
					  GROUP BY lk.id, ct.hanghoa_id, kq.ngay';
    	
    		$dt=$this->Kqbanhang->query($qstr);
    		
    		if (!empty($dt))
    		{
    			$row=0;
    			$col=0;
	    		foreach ($chitieudvs as $keyct => $valuect):
	    		$col=0;
	    		$tong=0;
	    			foreach ($loaikenhnvs as $keylk => $valuelk):
	    				for($i=0;$i<count($dt);$i++):
	    					if ($keyct == $dt[$i]['ct']['hanghoa_id'] && $keylk == $dt[$i]['lk']['id'])
	    					{ 
	    						$datanv[$row][$col]=$dt[$i][0]['soluong'];
	    						$tong +=$datanv[$row][$col];
	    					} 
	    				endfor;
	    				$col++;
	    			endforeach;
	    			$datanv[$row][--$col]=$tong;
	    			$row++;
	    		endforeach;
    		}
		/*
		 * table
		 */
		$ngay = date("Y-m-d");
		
		$cts = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id')));
    	$dabc = $this->Kqbanhangtt->find('list',array(
    												'conditions'=>array('chitieudv_id'=>$cts,'ngay'=>$ngay),
    												'fields'=>array('id')));
    	if (count($dabc)!=0)
    	{
    		$this->Session->setFlash(__('Báo Cáo Đã Được Lập',true));
    		$this->redirect(array('action' => 'hienkqngaycn',$donvi_id));
    	}
    	
    	$chitieus = $this->Chitieudv->find('list',array(
    												'recursive'=>0,
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    	$loaikenhs = $this->Loaikenh->find('list',array(
    												//'conditions'=>array('NOT'=>array('id'=>1)),
    												'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    	
    	
		
		$this->set(compact('chitieudvs','loaikenhnvs','datanv'));
    	$this->set(compact('donvi_id','col_ctdv','row_ctdv','thoigian','loaikenhs','chitieus'));
	}

	function suakqbanhangcn()
	{
		$donvi_id = $this->Session-> read('cur_cha');
    	/*
    	 * lay chi tieu duoc giao trong thang
    	 */
    	$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>date('Y-m-d'), 'ngaykt >='=>date('Y-m-d'),'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
    	$ngay=date('Y-m-d');
    	// rows
    	$chitieudv1s = $this->Chitieudv->find('list',array(
	    												'recursive'=>0,
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Kehoach.donvi_id'=>$donvi_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
		$dv = $this->Thuocdv->find('list',array(
										'conditions'=>array('donvi_id'=>$donvi_id),
										'fields'=>array('donvi','donvi')));
		$donvis = $this->Chitieudv->Donvi->find('list',array(
									'conditions'=>array('LEFT(Donvi.madv,2)'=>'TT','id'=>$dv),
										'fields'=>array('id','madv')));    	
    	
    	/*
    	 * lay kenh co bao cao trong ngay
    	 */
		$tt = key($donvis);
    	$lkngays= $this->Kqbanhangtt->find('list',array(
    										'recursive'=>0,
    										'conditions'=>array('ngay'=>$ngay,'Chitieudv.donvi_id'=>$tt),
    										'fields'=>array('loaikenh_id'),
    										'group'=>array('loaikenh_id')));
    	//cols
    	$loaikenhnvs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lkngays),
	    												'fields'=>array('id','loaikenh')));
    	$loaikenhnvs[]="Tổng";
    	$qstr='SELECT lk.id, ct.hanghoa_id, sum( kq.soluong ) as soluong
				FROM kqbanhangtts kq, chitieudvs ct, loaikenhs lk, thuocdvs tdv
				WHERE kq.loaikenh_id = lk.id
					AND tdv.donvi=ct.donvi_id
					AND tdv.donvi_id= '.$donvi_id.
					' AND kq.chitieudv_id = ct.id
					 AND kq.ngay = "'.$ngay.'" GROUP BY lk.id, ct.hanghoa_id';
    		$dt=$this->Kqbanhang->query($qstr);
    		if (!empty($dt))
    		{
    			$row=0;
    			$col=0;
	    		foreach ($chitieudv1s as $keyct => $valuect):
	    		$col=0;
	    		$tong=0;
	    			foreach ($loaikenhnvs as $keylk => $valuelk):
	    				for($i=0;$i<count($dt);$i++):
	    					if ($keyct == $dt[$i]['ct']['hanghoa_id'] && $keylk == $dt[$i]['lk']['id'])
	    					{ 
	    						$datanv[$row][$col]=$dt[$i][0]['soluong'];
	    						$tong +=$datanv[$row][$col];
	    					} 
	    				endfor;
	    				$col++;
	    			endforeach;
	    			$datanv[$row][--$col]=$tong;
	    			$row++;
	    		endforeach;
    		}
    		/*
    	 * lay data da bao cao
    	 */
    	$chitieus = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id')));
    	$kqbh = $this->Kqbanhangtt->find('list',array(
    												'conditions'=>array('chitieudv_id'=>$chitieus,'ngay'=>$ngay),
    												'fields'=>array('loaikenh_id','soluong','chitieudv_id')));
    	$kqbh_id= $this->Kqbanhangtt->find('list',array(
    												'conditions'=>array('chitieudv_id'=>$chitieus,'ngay'=>$ngay),
    												'fields'=>array('loaikenh_id','id','chitieudv_id')));
    	$chitieudvs = $this->Chitieudv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    	$lkngays= $this->Kqbanhangtt->find('list',array(
    										'recursive'=>0,
    										'conditions'=>array('ngay'=>$ngay,'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('loaikenh_id'),
    										'group'=>array('loaikenh_id')));
    	//cols
    	$loaikenhdvs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lkngays),
	    												'fields'=>array('id','loaikenh')));
    	$cot=0;
		$dong=0;
		if (!empty($kqbh))
		{
		foreach ($loaikenhdvs as $keylk=>$valuelk)
		{	
			$dong=0;
			foreach ($chitieudvs as $keyct=>$valuect)
			{
				$datadvsl[$dong][$cot] = $kqbh[$keyct][$keylk];
				$datadvid[$dong][$cot] = $kqbh_id[$keyct][$keylk];
				$dong++;
			}
			$cot++;
		}
		}
			//kq trung tam
    		$this->set(compact('chitieudv1s','loaikenhnvs','datanv'));
    		//kq da nhap
    		$this->set(compact('chitieudvs','donvi_id','datadvsl','thoigian','loaikenhdvs','datadvid'));
	}
	
	function hienkqngaycn($donvi_id=null)
	{
		if(!$donvi_id)
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		$ngay = date("Y-m-d");
		$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>date('Y-m-d'), 'ngaykt >='=>date('Y-m-d'),'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
    	/*
    	 * lay data trung tam bao cao hom nay
    	 */
		
		//$nhanviens = $this->Kqbanhang->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));
    	/*
    	$lks = $this->Phankenh->find('list',array(
	    										'recursive'=>0,'contain'=>array('kenh'),
	    										'conditions'=>array('Phankenh.nhanvien_id'=>$nhanviens),
	    										'fields'=>array('Kenh.loaikenh_id'),
	    										'group'=>array('Kenh.loaikenh_id')));
		*/
		//cols
    	$loaikenhdvs = $this->Loaikenh->find('list',array(
	    												//'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
    	
	   	//rows
    	$chitieudvs = $this->Chitieudv->find('list',array(
	    												'recursive'=>0,
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
    	
    	$data = $this->Kqbanhangtt->find('list',array(
    												'recursive'=>0,'contain'=>array('Chitieudv'),
    												'conditions'=>array('Chitieudv.donvi_id'=>$donvi_id,'ngay'=>$ngay),
    												'fields'=>array('loaikenh_id','soluong','Chitieudv.hanghoa_id')));
    	if(!empty($data))
		{
    	//$data
		$loaikenhdvs['tong']='Tổng';
    	$hang=0;
		$cot=0;
    	foreach ($chitieudvs as $keyct =>$valuect):
    		$cot=0;
    		$tong=0;
			foreach ($loaikenhdvs as $keylk =>$valuelk):
				if ($keylk!='tong')
				{
					$databc[$hang][$cot]=$data[$keyct][$keylk];
					$tong+= $databc[$hang][$cot];
				}	
				else 
					$databc[$hang][$cot] = $tong;
				$cot++;
			endforeach;
			$hang++;
		endforeach;
		}	
    	$this->set(compact('loaikenhdvs','chitieudvs','databc'));
	}

	function kqbhth_tuancn($donvi_id=0)
	{
		$kh = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach'),
    											'conditions'=>array('Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('Kehoach.thoigian_id'),
    											'group'=>array('Kehoach.thoigian_id')));	
    	$thoigian=$this->Thoigian->find('list',array('conditions'=>array('id'=>$kh)));
    	$this->set(compact('thoigian','donvi_id'));
	}
	
	function laykqbhcn1($donvi_id=0)
	{
		$this->layout='ajax';
		$ngaybd = $this->data['Kqbanhangtt']['ngaybd'];
		$ngaykt = $this->data['Kqbanhangtt']['ngaykt'];
		if (date('m',strtotime($ngaybd))!=date('m',strtotime($ngaykt)))
		{
			echo "<h2>ngày bắt đầu và ngày kết thúc phải cùng Tháng</h2>";
		}
		else
		{
			$donvi = $this->Kqbanhangtt->Chitieudv->Donvi->find('all',array('conditions'=>array('id'=>$donvi_id)));
			$ngaybd=date('Y-m-d',strtotime($ngaybd));
			$ngaykt=date('Y-m-d',strtotime($ngaykt));
			$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>$ngaybd, 'ngaykt >='=>$ngaykt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7)
    									));
			$chitieus = $this->Chitieudv->find('list',array(
    											'recursive'=>0,
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    											'fields'=>array('id')));
    		$kqbh = $this->Kqbanhangtt->find('list',array(
    												'conditions'=>array('chitieudv_id'=>$chitieus,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    												'fields'=>array('loaikenh_id','soluong','chitieudv_id')));
    		$chitieudvs = $this->Chitieudv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    		$loaikenhdvs = $this->Loaikenh->find('list',array(
    												'fields'=>array('Loaikenh.id','Loaikenh.loaikenh')));
    		
    		$kehoach = $this->Chitieudv->find('list',array(
    										'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    										'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Chitieudv.donvi_id'=>$donvi_id),
    										'fields'=>array('Hanghoa.tenhh','Chitieudv.soluong')));
	    	if (!empty($kqbh))
	    	{
		    	$cot=0;
				$dong=0;
				$loaikenhdvs['tong']='Tổng';
				$loaikenhdvs['kehoach']='Kế Hoạch';
				$loaikenhdvs['hoanthanh']='% Hoàn Thành';
				foreach ($chitieudvs as $keyct=>$valuect):
					$cot=0;
					$tong=0;
					foreach ($loaikenhdvs as $keylk=>$valuelk):
						if($keylk!='tong' && $keylk!='kehoach' && $keylk!='hoanthanh')
						{
							$datadvsl[$dong][$cot] = $kqbh[$keyct][$keylk];
							$tong +=$datadvsl[$dong][$cot];
						}
						elseif($keylk=='tong')
						{
							$datadvsl[$dong][$cot] = $tong;
						}
						elseif($keylk=='kehoach')
						{
							$datadvsl[$dong][$cot] = $kehoach[$valuect];
						}
						else
						{
							$datadvsl[$dong][$cot] = $tong*100/$kehoach[$valuect].'%';
						}
						$cot++;
					endforeach;
					$dong++;				
				endforeach;
	    	}
			$this->set(compact('loaikenhdvs','chitieudvs','datadvsl','donvi','ngaybd','ngaykt','donvi_id'));
		}
        $this->set(compact('ngaybd','ngaykt','donvi_id'));
	}
	
	function luyketonghop($kehoachid=null)
	{
		//if(!$donvi_id || $kehoachid)
		
		
		//$donvis = $this->Thuocdv->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('donvi','donvi')));
		
		$dt =$this->Kqbanhangtt->find('all',array(
								'recursive'=>0,
								'conditions'=>array('Chitieudv.kehoach_id'=>$kehoachid),
								'fields'=>array('Chitieudv.donvi_id','Chitieudv.hanghoa_id','Chitieudv.soluong','sum(Kqbanhangtt.soluong) as soluong'),
								'group'=>array('Chitieudv.donvi_id','Chitieudv.hanghoa_id')));
		
		$dvbc = $this->Kqbanhangtt->find('list',array(
								'recursive'=>0,
								'conditions'=>array('Chitieudv.kehoach_id'=>$kehoachid),
								'fields'=>array('Chitieudv.donvi_id')));
		
		$donvis = $this->Donvi->find('list',array('conditions'=>array('id'=>$dvbc),'fields'=>array('madv')));
		$chitieus = $this->Chitieudv->find('list',array(
											'recursive'=>0,
											'conditions'=>array('kehoach_id'=>$kehoachid),
											'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
											'group'=>array('hanghoa_id')
												));
		$cols= array();
		foreach($donvis as $keydv =>$valuedv)
		{
			$cols[$keydv][$keydv.'0']='Lũy Kế';
			$cols[$keydv][$keydv.'1']='Chỉ Tiêu';
			$cols[$keydv][$keydv.'2']='%Hoàn Thành';
		}

		$i=0;
	    $tong =array();
	    foreach ($donvis as $keydv =>$valuedv)
	    {
			foreach ($chitieus as $keyhh =>$valuehh)
		   	{
		   		for($j=0;$j<count($dt);$j++)
		   		{
		   			if ($dt[$j]['Chitieudv']['donvi_id']==$keydv && $dt[$j]['Chitieudv']['hanghoa_id']==$keyhh)
		   			{
		   				$luyketh[$keyhh][$keydv.'0']=$dt[$j][0]['soluong'];
		   				$luyketh[$keyhh][$keydv.'1']=$dt[$j]['Chitieudv']['soluong'];
		   				$luyketh[$keyhh][$keydv.'2']=round($dt[$j][0]['soluong']*100/$dt[$j]['Chitieudv']['soluong']).'%';
		   			}
		   		}
		   	}
		}
		foreach ($chitieus as $keyhh =>$valuehh)
		{
		   		$tong[$keyhh]=0;
		   		for($j=0;$j<count($dt);$j++)
		   		{
		   			if ($dt[$j]['Chitieudv']['hanghoa_id']==$keyhh)
		   			{
						$tong[$keyhh]=$tong[$keyhh]+ $dt[$j][0]['soluong'];		    				
		   			}
		   		}
		   		
		}
		$this->set(compact('chitieus','tong','luyketh','cols','donvis'));
	}

	function capnhatkq($kehoach_id=null)
	{
		/*
       	 * them , cap nhat lai so luong cua chinh nhanh
       	 */
		if(!$kehoach_id)
			$this->redirect(array('controller'=>'pages','action'=>'home'));
          
		$donvi_id = $this->Session->read('cur_cha');
          	
          	
        $cts = $this->Chitieudv->find('list',array(
    									'conditions'=>array(
    											'kehoach_id'=>$kehoach_id,
    											'Chitieudv.donvi_id'=>$donvi_id),
    									'fields'=>array('hanghoa_id')));
        $kqdaco = $this->Kqbanhangtt->find('all',array('recursive'=>0,
        					'conditions'=>array('chitieudv_id'=>key($cts))));
        
        $qstrs='SELECT lk.id, ct.hanghoa_id,ngay, sum( kq.soluong ) as soluong
				FROM kqbanhangtts kq, chitieudvs ct, loaikenhs lk, thuocdvs tdv
				WHERE kq.loaikenh_id = lk.id
					AND tdv.donvi=ct.donvi_id
					AND tdv.donvi_id= '.$donvi_id.
					' AND kq.chitieudv_id = ct.id
				 	GROUP BY lk.id, ct.hanghoa_id, kq.ngay';
         		
        $kqs = $this->Kqbanhangtt->query($qstrs);
        
        $lk = $this->Loaikenh->find('list');
        if(empty($cts))
        {
        	$this->Session->setFlash(__('Công ty chưa giao chỉ tiêu', true));
        	$this->redirect(array('controller'=>'kehoaches','action'=>'theodoichitieudv'));
        }
        // chua cap nhat kq lan nao
	    	if(empty($kqdaco))
	    	{
		   		$ngays= array();
		          	foreach ($kqs as $kq)
		          	{
		          		$ngays[$kq['kq']['ngay']]=$kq['kq']['ngay'];
		          	}
		          	
		          	foreach ($cts as $keyct => $valuect)
		          	{
		          		foreach ($lk as $keylk =>$valualk)
		          		{
		          			foreach ($ngays as $valuengay)
		          			{
					      		$record['Kqbanhangtt']['chitieudv_id']=$keyct;
					       		$record['Kqbanhangtt']['loaikenh_id']=$keylk;
					      		$record['Kqbanhangtt']['ngay']=$valuengay;
					       		$record['Kqbanhangtt']['soluong']=0;
					       		$this->Kqbanhangtt->create();
					      		if(!$this->Kqbanhangtt->saveAll($record))
					      		{
					       			$this->Session->setFlash(__('Không thể cập nhật',true));
					       		}
		          			}
		          		
		          		}
		          	}
	          }
	          
	          if(!empty($kqs))
	          {
		      		foreach ($cts as $keyct =>$valuect)
		       		{
			       		foreach ($kqs as $kq)
			       		{
			       			if($kq['ct']['hanghoa_id']==$valuect)
			       			{
			       				if(!$this->Kqbanhangtt->updateAll(array('Kqbanhangtt.soluong'=>$kq[0]['soluong']),
			       											array('Kqbanhangtt.chitieudv_id'=>$keyct,
			       												'Kqbanhangtt.loaikenh_id'=>$kq['lk']['id'],
			       												'Kqbanhangtt.ngay'=>$kq['kq']['ngay'])))
								{
					       			$this->Session->setFlash(__('Không thể cập nhật',true));
					       		}
			       			}
			       		}
		       		}
	          	}
          	
          	$this->redirect(array('action'=>'kqbanhangcn',$donvi_id));
	}

}